We have 20 furnaces and 10 presses, each has between 1 and 10 datapoints that are recorded.
How should these data points be saved in a SQL database?
Using transaction groups, all the data would be stored in one table with each data point being a column.
Would it be better to separate each furnace into its own table?
Or should I use the SQL Historian tag option?
Data is collected once per minute on the furnaces and every 5 seconds on the presses.
If there is a good amount of overlap in what the data points represent for each furnace, I would lean towards a setup with 1 transaction group per furnace, all writing to the same table, but inserting a row per group- using an action item to write an identifier for the furnace id.
You should try to align the columns as much as possible. For example, I imagine each furnace has a temperature data point. There should be one “temperature” column. You use the “furnace_id” column to specify which one you want to query.
You could use sqltags history, but this situation is pretty much ideal for transaction groups: a set of data that you want to record together, repeated for multiple devices.
Regards,
Thank you for the reply Colby.
What is an “action item”? I know I need a furnace ID, which I can get from the Furnace table in the database. Would you be able to give an example of using an action item?
Do I need to manually assign the ID? Should I use the historical transaction group or standard?
What about naming the columns in the database?
Should I have the temperatures recording into these columns (since a furnace can have a max of 6 points)
TimeStamp, FurnaceID, T1, T2, T3, T4, T5, T6
or TimeStamp,FurnaceID,F1T1, F1T2, F2T1, F3T1, F3T2, F3T3 (Furnace1 Temp1, Furnace1 Temp2, Furnace2 Temp1, …)
Yes, exactly. In your second option, the "furnaceid" column doesn't serve a purpose, you're encoding the furnace id into the column names (you may have not intended to include it). This is generally not considered good design- the first is preferred.
I guess "Action Item" is a bit of a dated term now- in Ignition they're called "expression items" in groups. The main point is that in each group that is storing data for a furnace, you'll need a way to specify the id. Expression items are likely how you would do this, as they can just be static values as well. So yes, I suspect you would manually assign them.
Hope that helps,