Solved

Database Design Question

Posted on 2009-05-17
4
278 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
  • 2
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now