1

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.

enter image description here

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

enter image description here

migrated from stackoverflow.com Jul 1 '15 at 0:48

This question came from our site for professional and enthusiast programmers.

  • How many measurements do you have for each system? I'm assuming a "system" is another word for statistical counter? – Vérace Jul 1 '15 at 0:55
  • No, sorry. A 'system' means some target that generates data. In this case it may be a server. Each one of these servers will generate ~100 data points each day. Hopefully the number of data points won't change, but they might. – ilya1725 Jul 1 '15 at 1:08
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.

1

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.

  • Thank you. @GordonLindoff, do you mind explaining the reason why having multiple tables with the same layout is usually an indication of a poor database design? – ilya1725 Jul 1 '15 at 2:30
  • @ilya1725 . . . From a database-design perspective, all entities should be in a single table. – Gordon Linoff Jul 1 '15 at 2:34
  • @GordonLinoff , perhaps what you meant was that "all entities with the same attributes should be in the same table"? :-) – Vérace Jul 1 '15 at 9:45
  • @verace . . . What I meant to say was: all instances of the same entity should be in the same table. There are nuances to this statement, but it is a good place to start -- and your clarification seems entirely accurate. – Gordon Linoff Jul 2 '15 at 2:40
1

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)
    )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.