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.

Problem:-
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…..

  1. 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.
  2. 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

  1. That he should be able to find out record through unique id.
  2. 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.
  3. 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

This info is not enough to provide you with any solution. You need to hire a professional for the job.

Put all data of the same structure in one table. As long as you have your data properly indexed the table size does not matter much.
Make sure that all repetitive fields (like product categories etc.) are either enum fields or in lookup tables.
Do *not* use phpMyAdmin for development. Learn how to use the command line interface. Then adopt a decent tool like Navicat or HeidiSQL.

(And of course Debasisas is right. You just don't know enough for such a job.)

This info is not enough to provide you with any solution. You need to hire a professional for the job.

Please tell me what information you need more? i will tell you all so that you can suggest me some thing
thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.