Multi-table optimal database design I'm a bit new to the database design. Thus this question to people who may have more experience.

I need to design a database that needs to store statistical data for many systems. The data is collected every day. There may be a couple hundred statistical counters. The number of systems can also grow.

Which database design is more efficient? From long term maintenance standpoint, from performance standpoint, etc.


Design 1: one giant table with columns for counters. Then each system on each date will add its number of entries.





Design 2: every system gets its own table dynamically created. It will contain a line for every day of statistics collection. There will also be one more table of tables that will contain the list of all system tables.
Of the two options mentioned, without any doubt, the first is better.  MySQL is fine at handling large tables.  There is no reason whatsoever to break the data into separate "equivalent" tables.  In fact, having multiple tables with the same layout is usually an indication of a poor database design.

For performance, you can then add indexes on the system and date (or both in one index).  You can also learn about performance.

There are some reasons why you would split the data into separate tables per system.  Here are two:


If the statistics maintained for each system were very different from other systems, then it would be more efficient to store only the appropriate columns.
If system requirements mandated data separation or permissioning.  The latter is easier to do on the table level.


You might also consider an entity-attribute-value (EAV) approach.  This would be a table where you basically have four columns:


date
system name
statistic name
value


You then repeat rows for each statistic.  This is usually less efficient than the single-table approach.  But it can be beneficial, particularly when you have lots of statistics, and most systems have different sets of statistics.