Database which increasea every month, which design strategy should I use?

Question

I have a database that increases every month. The schema remains the same, so I think I use one of these two methods:

• Use only one table, new data will be appended to this table, and will be identified by a date column. The increasing data every month is about 20,000 rows, but in long term, I think this should be problem to search and analyze this data

• create dynamically one table per month, the table name will indicate which data it contains (for example, Usage-20101125), this will force us to use dynamic SQL, but in long term, it seems fine.

I must confess that I have no experiences about designing this kind of database. Which one should I use in real world?

Thank you so much

Answer 1

Look into partitioning your table.

That way you can physically store the data on different disks for performance while logically it would be one table so your database stays well designed.

Answer 2

20 000 rows per month is not a lot. Go with your first option. You didn't mention which database you'll be using, but SQL Server, Oracle, Sybase and PostgreSQL, to name just a few, can handle millions of rows comfortably.

You will need to investigate a proper maintenance plan, including indexing and statistics, but that will come with lots of reading and experience.