Is naming tables september_2010 acceptable and efficient for large data sets dependent on time?

Question

I need to store about 73,200 records per day consisting of 3 points of data: id, date, and integer.

Some members of my team suggest creating tables using month's as the table name (september_2010), while others are suggesting having one table with lots of data in it...

Any suggestions on how to deal with this amount of data? Thanks.

========== Thank you to all the feedback.

Answer 1

Seems like it should be just fine holding everything in one table. It will make retrieval much easier in the future to maintain 1 table, as opposed to 12 tables per year. At 73,200 records per day it will take you almost 4 years to hit 100,000,000 which is still well within MySQLs capabilities.

Answer 2

I recommend against that. I call this antipattern Metadata Tribbles. It creates multiple problems:

• You need to remember to create a new table every year or else your app breaks.
• Querying aggregates against all rows regardless of year is harder.
• Updating a date potentially means moving a row from one table to another.
• It's harder to guarantee the uniqueness of pseudokeys across multiple tables.

My recommendation is to keep it in one table until and unless you've demonstrated that the size of the table is becoming a genuine problem, and you can't solve it any other way (e.g. caching, indexing, partitioning).