I thinking about a project to build up a web based application. I am not able to understand what should be structure of the database.
Data is in bulk which is of a big general store, and they have 50,000 transaction minimum each day. They store all data and assign them a unique id. Number of columns are almost 45 and they are providing me data in excel sheet. And I have mentioned that each file have minimum 50,000 entries and 45 columns. And number of excel files are almost 500.
Now I think I have two options…..
- that I should store all data in one table, so its mean I have to store 250,0000 minimum in one table, but I don’t know what will be effect on speed, where as I have to use Php and MySql within built in Phpmyadmin. And later on they wanna add more record of each day. Mean data will continually increase.
- That I should create a new table for each file, its mean I have to create 500 tables minimum for each file or for each day record. But in this case what will be name of tables, weather like this one (2012-12-30) and so on for each table or something else….?
Now please help me out and tell me what should structure of the database as well as tell me very important thing what queries I should set to print record on screen because my client is demanding three things for searching
- That he should be able to find out record through unique id.
- He should able to find out record through data, mean he will put two dates in this format (year-month-date) and mysql will provide data which exist between both of these dates.
- He should be able to find record through combination of unique id and dates, mean he will put id as well as date and mysql will provide them that data of single entity which exist only between these dates.
Please guide me. Thanks