I am working on a project where I need to store quality check data in a database for production purposes, we are changing from paper forms to an Ignition perspective application.
Each check consists of 16 tests, the datetime value (time interval integer) and work order , besides some additional information. The checks need to be recorded every 30 minutes, but I would like to store the data in a compact way so that it can be easily queried and analyzed later. Additionally, I am looking for advice on the best way to store and query the data for a single shift (8 hours), rather than having a new entry for each 30-minute interval.
here is an example of how the current structure looks like
Any suggestions on how to efficiently store and query this data would be greatly appreciated.
That's not much data. I recommend storing each check separately, with enough ancillary information to audit it later (as needed). Use a PIVOT to make the 16 half-hour columns when querying.
2 Likes
I forgot to mention that this will be running on approx. 30-40 machines. Should I create a table for every instance?
No, include a machine ID column in the one table.
4 Likes
Should I make a column for machine name and one for machine number? or one for machine ID and use a dictionary?
I would make a column just for a machine ID. Use a separate table holding machine ID & Name combinations, with ID as its primary key. You can then include name in queries using a join.
1 Like
I would recommend reading up on normalization in the context of databases, there's not much to it and it will help you design cleaner databases that are easier to work with.
4 Likes
