Creating a table with the name being a variable date?

Question

I wanted to create a table with the name of the table being a date. When I gather stock data for that day, I wanted to store it like this:

$date = date('Y-m-d');
$mysqli->query(
    "CREATE TABLE IF NOT EXISTS `$date`(ID INT Primary Key)"
);

That way I will have a database like:

2013-5-1: AAPL | 400 | 400K
          MFST | 30  | 1M
          GOOG | 700 | 2M
2013-5-2: ...

I think it would be easier to store information like this, but I see a similar question to this was closed.

How to add date to MySQL table name?

"Generating more and more tables is exactly the opposite of "keeping the database clean". A clean database is one with a sensible, normalized, fixed schema which you can run queries against."

If this is not the right way to do it, could someone suggest what would be? Many people commenting on this question stated that this was not a "clean" solution?

Answer 1

Do not split your data into several tables. This will become a maintenance nightmare, even though it might seem sensible to do so at first.

I suggest you create a date column that holds the information you currently want to put into the table name. Databases are pretty clever in storing dates efficiently. Just make sure to use the right datatype, not a string. By adding an index to that column you will also not get a performance penalty when querying.

What you gain is full flexibility in querying. There will be virtually no limits to the data you can extract from a table like this. You can join with other tables based on date ranges etc. This will not be possible (or at least much more complicated and cumbersome) when you split the data by date into tables. For example, it will not even be easy to just get the average of some value over a week, month or year.

If - and that's depending on the real amount of data you will collect - some time in the future the data grows dramatically, to more than several million rows I would estimate - you can have a look at the data partitioning features MySQL offers out of the box. However, I would not advise to use them immediately, unless you already have a clearly cut growth model for the data.

In my experience there is very seldom a real need for this technique in most cases. I have worked with tables in the 100s of gigabytes range, with tables having millions of rows. It is all a matter of good indexing and carefully crafted queries when the data gets huge.