?
Solved

Database Design Question

Posted on 2009-05-17
4
Medium Priority
?
289 Views
Last Modified: 2012-05-07
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
Comment
Question by:cdbuch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 24407972
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
 

Author Comment

by:cdbuch
ID: 24408084
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 2000 total points
ID: 24408341
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
 

Author Closing Comment

by:cdbuch
ID: 31582438
Thank you for your help! :)
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this article, we’ll look at how to deploy ProxySQL.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question