2

I have a question regarding my database scheme:

Lets assume I have 100+ measurement devices, each of them is equiped with a voltage and current sensor. Multiple (50-100) of those devices are aggregated in a bigger device (DEVICE) Shall I create a mysql table for each device and each quantity and store the values like:

DEVICE1_voltage:
Timestamp, sub_device1_value, sub_device2_value, sub_deviceX_value...

DEVICE1_current
Timestamp, sub_device1_value, sub_device2_value...

DEVICE2_voltage:
Timestamp, sub_device1_value, sub_device2_value, sub_deviceX_value...

DEVICE2_current
Timestamp, sub_device1_value, sub_device2_value...

...

Or is a relational scheme like:

DEVICES:
id, name

sub_devices:
id, DEVICE_id


voltage_reads:
id, timestamp, sub_device_id, value

current_reads:
id,timestamp, sub_device_id, value

more appropriate? The advantage I see with version 2 is that my scheme will never change, as with version 1 I get two new tables with each new device. These Tables then have different numbers of colums as they differ in sensor setups.

The big disadvantage of version 2 is see is when I get a lot of measurements and the table grows very big....plus that when I query Data this is already the format I need later

Which scheme is better in "theory"?

Thanks in advance!

Edit: Renamed tables and colums, to make more clear that each device can have multiple sub devices

5
  • Dynamic information should always be in table cells, not table or column names.
    – Barmar
    Nov 1, 2017 at 22:24
  • So you would also suggest to go with version 2? Or is there a smarter solution?
    – Mogli456
    Nov 1, 2017 at 22:48
  • I don't understand why you have two devices tables in version 2.
    – Barmar
    Nov 1, 2017 at 23:16
  • I updated the question to make more clear that I have DEVICES that can have sub_devices. When I have 100 subdevices, I have to insert 100 rows with the same timestamp in the readings table, in vers.1 this would be only one row with 100 columns
    – Mogli456
    Nov 2, 2017 at 8:28
  • Don't be afraid of inserting lots of rows, databases are designed to handle that.
    – Barmar
    Nov 2, 2017 at 15:18

2 Answers 2

1

You definitely should not have one table per device. That means you'll have to create dynamic SQL to merge the device name into the query, and create a new table every time you add a device. So version 2 is much more appropriate than version 1.

But you don't necessarily need separate tables for voltage and current. You could have a single table device_log, with columns:

  • id - primary key
  • sub_device_id - foreign key to sub_devices table
  • timestamp
  • type - 'voltage' or 'current'
  • value

Or if you don't need so much generality, and always read current and voltage at the same times:

  • id - primary key
  • sub_device_id - foreign key to sub_devices table
  • timestamp
  • voltage
  • current
0

I definitely would choose version 2. The problems I see with version 1:

  • every setup change would require an ALTER TABLE, which - especially with MySQL- is an expensive and slow operation
  • you only can write a row when you have all values from all devices (or you have to update a lot and require timestamps for each device).
  • MySQL has a row size limit which you might hit with hundreds of columns (https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html#row-size-limits)

That said, it still might depend on how you need to read the data. If you read it a lot on the fly and need each value per device in a column, version 1 might give you simpler and faster SELECT statements.

1
  • The three bullet points are no big deal, there will not be more than 200 sub_devices, the setup will not change and I get the values simultaneously. But the easier select is true, basically I need the data as it is stored in vers. 1. But I really do not like having the IDs in the table name...and having a growing number of tables
    – Mogli456
    Nov 2, 2017 at 8:25

Your Answer

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