I would rather create a Sheet table with a status column, and let the production table and the control_measures table refer to it instead.
I wasnt clear in my explanation but that was what i meant on how to solve the normalization.
Why do you move the "Sheets" to a new table, it's still the same entity, isn't it?
I will try to explain.
The production table stores shifttime and orders.
So a new record will be added on new shifts or new order.
When they are producing the final product, they also measure,controls and package sheets in stacks.
So instead of having sheets stored over order/shift I want to store it on each stack.
(One record for each stack in control_measures which is related to their shifts production table).
Storing sheets in control_measures gives higher "resolution" and better data to serve to our customers.
But only possible when producing the final product.
Temperature data is first written to a holding table - LocationID int, Hour int, whichVariable int, value float. Then it is merged into the main table (using a MERGE statement)
All this works fine when the temperature data written to the holding table goes in one variable at a time, executes the MERGE, loads the next variable, etc.
If I have multiple programs adding multiple locations and different variables all at the same time, the system deadlocks. I could force one instance of the outside program inserting the data to use a mutex to guarantee undisturbed calls to MERGE but it is quite possible we will have multiple instances of the outside program running.
What is the best approach to take to allow merging of the data?
The routine uses the MERGE statement to determine if a record for the particular observation exists. If it does not, it inserts a new record. If a record does exist, it updates the appropriate field.
Problem comes in that there are three raw import records for the particular observation. So the first merge may try to insert something for a high temperature while at the same time the system is trying to insert for the low temperature.
I can use a cursor to cycle through the imported data row by row but want to avoid the speed hit.
I also can't change the main table to hold a record for each individual type of temperature because of the huge number of records (~17 million per observation cycle)