Help in good Database Design

[pankaj.garg]

Dear All,

I need your suggestion in developing a Database - I will use MYSQL as database. My database will have below specifications -

- 1 table "Table-1" with 2 columns - DateTime and Integer Value
- 1 table"Table-2" with 5 Columns - columns having process details (i am doing some testing and each test performed is called as process)
- for every process "Table-1" will have 3500 entries and "Table-2" will have only 1 entry
- I have 5-10 process per day
- I have to maintain data for entire Year.

Now please suggest which of the following should I do ?
1. Add a column (to identify the related process) and Keep adding data to my Table-1 for all processes. This will make my Table-1 very large
2. Make a separate Table-1 for every process. This will result in lots of tables
3. I make a Table-1 every month and keep track of process dates in my Table-2

Please let me know if any of above is good solution or should I have a different approach?

Thanks in advance

Pankaj

[BitBlt]

It depends on how you want to use the data from Table-1 afterwards.

If you are going to query the details all year long, then Option 1 is probably best. If you only care about the process details for 1 month and then only care about counts and summary, then option 3 is best, with a monthly job to summarize/delete.

Or, if you don't care about querying the data from Table-1, you could just have the process details spew out as a log file and dispense with storing Table-1 in your database entirely.

Hope this helps! Good luck!

[pankaj.garg]

Thanks BitBlt for the reply.

I will have to query the Table-1 for whole year. I might be asked to retrieve data from Table-1 for any specified process.

My apprehension is - if my Table-1 becomes too large, will it become difficult to query data from Table-1 ?

Thanks

[urtrivedi]

If you are not going to use data after one year, at the end of year, can trunc year old data. to keep on required rows.
I think mysql will handle millions rows without any problem.

[BitBlt]

...if my Table-1 becomes too large, will it become difficult to query data from Table-1 ?

The flippant answer would be "No, querying is the same...it just takes longer to get the result set!"

The real answer is "It depends". Mostly it depends on what your data retrieval needs are. If you are going after specific subsets, then a few judicious indices will speed your queries. If you are doing full-table scans or reads, it just means there's more data to return. If you're doing lots of slicing-and-dicing, grouping, summarizing, etc. then periodic ETL of some ancillary summary tables or to a star schema will help.

Bottom line is that if you have large tables (and 1-2 million rows isn't really that large these days) you can do a lot by moving to bigger hardware, breaking up your data into separate physical disk spindles, partitioning, appropriate use of the different MySQL engines, etc. An experienced MySQL database administrator might be able to give you specific advice when you run into actual difficulties.

Hope these hints are useful to you. Good luck!

[pankaj.garg]

Thanks urtrivedi and BitBlt for the responses.

I will go for single table-1.

Thanks & Regards,

Pankaj