I have been tasked with building a batch tracking system and just wanted to get some experts' opinions on my design.
The database needs to track batches which can have n number of ingredients. The starting and stopping time for each batch must be recorded and the final weight totals for each one.
BatchID (PK, Identity)
IngredientID (PK, Identity)
Series of Events:
1) Program would call a stored procedure to start a new batch
2) Each ingredient added to the database would be attributed to current batch
3) Program would call a stored procedure to end the current batch
I am somewhat worried about data integrity, batches not being completed properly and the like. Is there a better way to do this, or I am on the right track?
Thanks in advance for any advice.