Multi-table optimal database design

Question

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.

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

2. 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.

Answer 1

You say 100 measurements once a day. I'd have two tables as follows:

CREATE TABLE Measurement
(
  Measurement_ID INTEGER PRIMARY KEY,
  System_ID INTEGER,  -- FK into the System table
  Measurement_Date DATE (or DATETIME depending),
  Measurement_1 M1_Datatype,
  ..
  .. 100 lines
  Measurement_100 M100_Datatype
);

CREATE TABLE System
(
  System_ID INTEGER PRIMARY KEY,
  System_Location VARCHAR(3) -- maybe a code, if that suits? Zip?
  System_Description VARCHAR(50)
);

@GordonLindoff is right about it being feasible to put all this data in a single table - one system's measurements are like anothers - they're the same thing - objects which have similar attributes belong in the same table (unless your storage requirements become truly massive). Plus with a 64 bit integer as a key, you'll effectively never run out of potential PRIMARY KEYs.

See my accepted answer here for good reasons to never consider an EAV system.

Answer 2

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:

1. 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.
2. 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.

Answer 3

since your date will grow rapidly. I would suggest to separate all the 'character field' into dimension and link with numeric values in the fact table which will only contain numeric data. This approach will be extremely fast and save you tone of space. also, depending on the partitioning configuration you can make it more efficient for retrial

    create table dim_system (
        system_id int PK
        system_name char    
    )

    create table dim_counters (
        counter_id int PK
        counter_name char
    )

    create table fact_measurements (
        id int 
        datetime datetime
        system_id FK( dim_system.system_id )
        counter_id FK( dim_counters.counter_id)
    )