Mysql – Multi-table optimal database design

database-designMySQL

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

Best Answer

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.

Related Question