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
devices
tables in version 2.