I many not fully understand what you’re trying to accomplish so please excuse me if my following thoughts are irrelevent.
One approach is to have a table for each machine. Each table would have the standard index and time stamp along with a column for each motor. The tables would have different numbers of columns. For example:
Table Machine203 (for Machine 203 with 3 motors)
Machine203_ndx, motor_1_speed, motor_2_speed, motor_3_speed, t_stamp
Table Machine204 (for Machine 204 with 4 motors)
Machine204_ndx, motor_1_speed, motor_2_speed, motor_3_speed, motor_4_speed, t_stamp
Another approach is to have one table and columns like this:
Table Machine (1)
Machine_ndx, Machine203_motor_1_speed, Machine203_motor_2_speed, Machine203_motor_3_speed, Machine204_motor_1_speed, Machine204_motor_2_speed, Machine204_motor_3_speed, Machine204_motor_4_speed, t_stamp
Yet a third approach is to have one table and columns like this:
Table Machine (2)
Machine_ndx, MachineID, MotorID, Speed, t_stamp
Each approach has its advantages and disadvantages. The best approach depends on how the data is generated and in what form, and what you intend to do with the data.
The first approach is straight forward. It may be harder to generate boiler plate windows as the table to query would differ with the machine. It also means that charts with data from multiple machines will be a little slower to update because multiple tables will have to be hit. It’s also going to be more of challenge to join data from multiple tables based on time for reports or exporting since there is not guarantee that time stamps will match exactly. If additional machines are added, you just add additional tables. If additional motors are added to a machine, you just add new columns to the appropriate table. Null values will be written for existing rows.
The second approach may be best for reporting data for multiple machines over time in reports and charts. It means you only have to hit one table to get the data and t_stamps are guaranteed to be consistent amongst different machines/motors. Additional columns must be added if additional machines and/or additional motors are added. You have to make sure that the database can handle the required number of columns. If the column count approaches the column limit, additional tables may have to be created, in effect, making this approach the same as the first. In this scenario though, you have a less than obvious idea where a particular machines data resides. This approach doesn’t lend itself well to boiler plates unless you use sophisticated expressions to generate dynamic SQL queries. When you add more motors, the database engine will have to add null values for any existing rows.
The third approach works best for boiler plate windows but is more difficult to use with trending. The data must also be available in a manner which is not likely to occur naturally or easily generated. Again, joining data from multiple machines, and motors, based on time would be a challenge. In terms of columns not having null values, this approach is the most efficient. But in terms of the number of rows, it’s the least efficient.
Depending on which approach you take, some of the short comings of the design can possibly be overcome by automatic post processing within the database. Or you could use a combination of approaches (i.e., have two tables of different design for the same machine). A dual approach may not be the most space efficient but it may greatly speed up queries or simplify your FPMI application.
Just some food for thought…