... many tables, all the same fields, but the names must change based on the date.
I would strongly
To anyone familiar with spreadsheet products, like MS Excel, this "Table-Per-Thing
" Model seems a "reasonable" and "logical" choice.
To anyone that knows Databases
, it is not.
" Model always breaks down at some point.
You can achieve a far better result by using a single
table to which you add that qualifying datetime column. With proper indexing, you will get performance that's just
as good as "Table-Per-Thing" promises but will give you far
fewer headaches, in the long run.
For example if you needed to summarise your data across `n` years, then you'd need `12 * n` tables in the query - MySQL is [still] limited to only 61
tables in any one query (including
those in any views). If you want more than five years of Data in this scenario, you just can't do it.
Properly indexed, through, a single table will easily handle millions
of rows of data. There is simply no need
to split it up like this.
Regards, Phill W.