Solved

Database Design Question

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

Title # Comments Views Activity
SQL date incremented 11 31
SQL Server Designer 19 42
What's wrong with this T-SQL Foreign Key? 7 46
Need help how to find where my error is in UFD 6 30
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 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