I plan to do a database for more machines. Each machine will have multiple sensors mounted that store data like temperature, current, power, fault, manual mode and so on every minute. Each machine consists of multiple parts and the same data sets are stored for each part.
Every part may be divided into two subparts and each part/subpart can have multiple sensors for temperature. So in reality system can cosists of six parts, only part five has two subparts. Each part or subpart has one sensor for a variable stored except temperature. So for example part1 will have one variable for power, current, voltage, manual mode but can have multiple sensors for temperature.
There can be a system with more than 500 variables recorded every minute, and each variable type has a different type of value, e.g., some are integers, some are floats with one decimal some with two decimals. I can't setup one column for this. That's why I want to make the best structure possible but I have never really performed database design since I have always used only simple tables with small data.
I just don't want to make a table with 500 or more columns since I suppose that is not a good idea.
I would do something like this:
sensor_name, type, value, timestamp
I know according to the sensor name from what part/subpart is the reading.
However, with a large machines this table will have like 500 rows for every minute.
Thank you for every idea sugested.
