Large number of storage / insertion of records in MySQL

[ajitsunny]

Please suggest for following issues for a web based application in ASP.NET 3.5: 1. There is requirement for approx 10 to 20 million records entry in the database daily, or we can say that there would be around entry of 10 lakh records per table on daily basis. What would be the best Database Design, Model & Architecture in MySQL database. Need proper optimization for quick retrieval, insertion, updates & deletion. Also provide the hardware detail that would required to handle such a large database in long term. 2. There is a scenario of inventory management of advertisements on daily basis, if there are 100,000 locations and user booked these locations for 1 year then I need to insert/update 100,000 x 365 = 36500000 records/rows in the table to maintain daily inventory. Please suggest better solution & table structures. [Database Design, Model & Architecture for Large number of storage / insertion of records in MySQL]

[Lost User]

It's part of the design. Consider this as an example; If you have a Location-table like this;

Location Name

---

  21    Floor
  22    Cellar
  23    Roof

and a User-table;

Id Name

---

64 ajitsunny
66 E. Codd
67 Don Quichotte

, with a Bookingstable like this;

Id BookedByUserId BookedLocationById BookingStart BookingEnd

---

1 64 21 11.11.2011 12.11.2011
2 64 21 14.11.2011 16.11.2011
3 67 23 11.11.2011 13.11.2011
5 66 21 23.12.2012 31.12.2013

Then you can encode a booking with a single command, providing start and end-dates as parameters :) (e.g., like;)

INSERT
INTO Booking
VALUES ( 64, 21, '2011-11-11T00:00:00.000', '2011-11-12T00:00:00.000' )

[Robin_Roy]

If you need quick retrieval of data, a de-normalized database is often a good practise, as if you have normalized dB, there would be lots of joins,which are actually costly in terms of perfomance of queries. On the other hand, if the preference of the application is user filling up form data and you dont want any redundancy in the database, then you should normalize the database tables. It totally depends on the application need.

[ajitsunny]

I my case we need to optimize two tables to stores very large number of records (in millions / billions) - just change the table structure and apply horizontal partitions on date range or no. of records. The new table structure should be able to minimize the records required to store. 1. location_BookedAdvertisement (loc_id, advt_id, start_date, end_date, inventory_text) And data would be like this for location_BookedAdvertisement table: (12, 132, 2009-07-20, 2009-07-25, '1000~2000~5000~8900~3000') This structure reduces the number of rows required to store 100,000 location's date wise assigned inventory by 364 times. Now I only need to store 100,000 rows instead of 100,000 x 365. So I am giving this table structure OK DONE. Also partition this table on no. of records such as 10 millions rows per partition. 2. Now change the structure of tinventory table.. location_ inventory (loc_id, date, Total_available_inventory, booked_inventory) .. to location_ inventory (loc_id, year, Jan1, Jan2, Jan3, Jan4 ,.. ..., Dec29, Dec30, Dec31) And data would be like this for location_ inventory table: (12, 2009, '5000~2000', '10000~8900',... ...,'12000~2000','12000~2000','12000~2000') Here within date column we have 'Total_available_inventory~booked_inventory'. I know there would be 366 column in one table... but today I tested it.... SELECT / INSERT / UPDATE works very very fast as compared to in the earlier table structure. I'll partition this table year-wise i.e based on year column. And this table structure again reduces the required number of records by 364 time... i.e. to store one year inventory of 100,000 locations I would need to store only 100,000 rows. This solution is acceptable.