Search code examples
databasedatabase-designdatatablesbcnf

Is it ok to create a new database table for each metric?

My engineering team builds a machine and records various metrics related to the machine such as battery voltage, name of the machine, number of times used etc. My current database structure has the following columns in one table

ID
Name of the machine
# time used
battery voltage
.
.

The team keeps changing the names of the machine or the metrics and they suggest that every time there is a name change a new table should automatically be created to avoid any code breaks. Eg. If the initial name was A1/BatteryVoltage the table would be

Id
A1/BatteryVoltage

Later if they change it to A1/Battery_Voltage, they want a new table to be created with following columns

ID
A1/Battery_Voltage

I have a sense that this doesn't make sense as it can bombard the database with huge number of tables. But my manager is asking me to be more concrete on why I think this doesn't make sense. I know that cost of creating tables is not much but I also know that I cannot create filters such as machine name in pulling metrics when I use this structure. I also know that I will have to create multiple joins to get one single metric. But is there anything else that can help me in convincing my team or convince myself about what my team wants?

Solution

  • Create one table for everything, adding columns to discriminate metrics. Something like:

    create table metric (
  id int, -- eg auto increment
  created timestamp,
  machine_name text, -- eg 'A1'
  attribute text, -- eg 'Battery_Voltage'
  value text -- eg '9'
)

    Now you never have to do anything to cater for new attributes, or attribute name changes.