• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Database Design Question

Hello,

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.

Batch
=================
BatchID (PK, Identity)
BatchStart
BatchEnd
BatchTotalWeight
...

Ingredient
=================
IngredientID (PK, Identity)
BatchID (FK)
IgredientName
IngredientWeight
...

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.

--Charly
0
cdbuch
Asked:
cdbuch
  • 2
  • 2
2 Solutions
 
chapmandewCommented:
Sounds like you're on the right track.  Instead of having your BatchID in your ingredients table, I would remove it and create a BatchIngredients table where you store that information.  That way, your ingredients table has only ingredient related information, and your batch ingredients table contains your BatchID, IngredientID, etc.  Does that make sense?
0
 
cdbuchAuthor Commented:
Something like this:

Batch
=================
BatchID (PK, Identity)
BatchStart
BatchEnd
BatchTotalWeight
...

BatchIngredients
=================
BatchID (PK, FK)
IngredientID (PK, FK)

Ingredient
=================
IngredientID (PK, Identity)
IgredientName
IngredientWeight
...

Right?  I plan on using table insert triggers to associate each new ingredient with it's batch, but it's been awhile.  Are there any gotchas I should watch out for?  I'm assuming that I can insert a row into BatchIngredients during an insert trigger on the Ingredients table to get my association?

Thanks for the prompt response!

--Charly
0
 
chapmandewCommented:
Yes, that is what I am talking about.  That way, your tables do specific things...would really make sense to have the BatchID in the ingredients table...would create a lot of duplicate values when it isn't needed.

Looks good.  Sure, you can insert a row into batch ingredients when those events occur.
0
 
cdbuchAuthor Commented:
Thank you for your help! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now