Will my table design cause future problems
-----Original Message-----
"Aaron" wrote in
message
...
(snipped)
A research facility mixes elements(LotID), processes
them,
evaluates the end product and then uses it in a part
which
will also get tested.
So:
- each part has one (or several?) tests? several types of
tests
- each part comprises several end products? The combined
elements(LotID) can be used in several Part#'s. Each Part#
uses up to 2 LotID's(Front and Back) but the Front LotID
is what affects the Part#'s performance.
- each end product comprises several elements? Yes
Correct?
tblComposition (Lot# can be made up of 1-? elements)
LotID (FK to tblManufacturing)
Element (chemical or metal)
ElementRatio (amount)
What is the primary key of that table? Actually I have a
autonumber CompID PK as well as a multi-field index
between LotID and Element.
What does one row in that table, represent?
Row1: Auto#,LotID112233,Al,60
Row2: Auto#,LotID112233,Cu,40
The concatenated result would be LotID112233,AlCu,60:40
tblManufacturing
LotID (PK)
ProcessEquip
RunDate
%Source
SourceID
What does one row in that table, represent? The combined
elements from above are mixed with a source element to
produce the final combination which will be used in a part.
tblEvaluation (characterization of LotID's after mfg)
LotID (PK)one-one relationship to tblMAnufacturing
ParticleSize
ECValue
FinalMAss
What is the primary key of that table? LotID is actually
the PK
What does one row in that table,represent? (I don't
understand: "characterization of LotID's after
manufacturing". You need to describe the table in terms of
the entitites you
have mentioned so far: parts, tests, elements, end
products, etc.) The combined/processed elements are tested
(characterized) before being used in Parts. Each row will
contain the results for a LotID
tblParts
Part# (PK)
LotID (FK to tblManufacturing)
Account
Performance (test data of Part#)
Aaron, that is the only table that is clearly described
so far. Each row in
that table represents a single, unique Part. Each part is
identified by a
unique Part#. Part# is the primary key of that table. We
need the same level
of understanding of your other tables, before we can
comment.
HTH,
TC
One of the key deliverables from this database is to
provide Evaluation data and Part listings for every
combination of Elements and %Source information. i.e;
LOT#AALL12 had a 10%Source and was made up of
Al,Si,Cu at
a ratio of 60,20,20 and was used in
PART#1234,PART#5678,etc
If I continue down this path I will need to
concatenate
the records in the tblComposition and I don't know
how big
a problem that will be. I also don't know what other
problems I am not considering. Please advise. Thanks.
.
|