Quote
... many tables, all the same fields, but the names must change based on the date.
I would
strongly advise
against doing this.
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.
The "Table-per-
Thing" 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.