Table relationships

[KS]

I have the following situation:
A product is made and data is collected:
ProductID
DateMade
MaterialUsed
EquipmentID
Temperature.........

Then that product can be processed further and additional
data collected:
ProductID(new)
ParentID(old)
Temperature(can be up to 4 diff temps)
TimeAtTemp(for each temp)
#Containers (can be up to 4)
VolumePerContainer(for each container)

This new product needs to inherit all the original data
from the parent.

The products can then be used in a part and they need to
trace back to how the product was made.
Part#
ProductID
ParentID(or some indication that it had a parent)
DateMade
MaterialUsed

What's the best way to setup my tables especially for
the "inherited" properties? If I have 2 tables it seems
like my "genealogy" queries will get pretty complicated.
If I have 1 table how do I autofill the inherited
information for the child(post-processed product).
What about the Temperature and #containers data since it
can vary from 1 to 4 values?
Temp1, Temp2, etc violates normalization rules, right?

I'm going around in circles!!!! If this makes sense to
someone out there I would appreciate some suggestions.
Thanks in advance for your help.

 

[Rebecca Riordan]

This will be easier if you tell us something about what it is you're
modelling....are these airplane component or buttercream icing? What
processes are occurring...are you tempering steel or chocolate? What sort
of parts get made? Most importantly, why do you need this information, how
will it be used?

But I'll have a stab at it--first off, don't change the ProductID. What you
have is a ProductID (with all those attributes that get inherited), and a
ProcessID. You need to add a date to the ProcessID table, I think, in order
to sequence them. Or maybe a sequence number would be more appropriate.

Second, NEVER include "up to 4 diff temps" in a single field. You need to
split that out as a separate table.

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[KS]

We are dealing with electronic components. The information
will be used to determine what manufacturing conditions
and material combinations produce the best performing
parts.