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