Use one big table or multiple small tables for described scenario

Question

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

Answer 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

Answer 2

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.