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
Solved

Database Design Question

Posted on 2009-05-17
4
281 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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