Please help

[BG]

Here's my quandary:
In our research facility, processing of product involves
several steps. The first 2 steps are the same for all
product. I capture that information in 2 tables;
tblComposition and tblManufacturing. TblComposition has
fields for LotID, Component, and Amount of Component (a
LotId can have 1-8 components). TblManufacturing has
several fields including LotID, MfgEquip, Operator, etc.
Some Lots are split out at this point to "child" Lots and
receive additional processing, tblPostProcessing with
fields LotID,ParentID,PPEquip, etc.. Both parent and child
go on to get tested and assembled. When I run queries on
the end product I need to know the Composition and
Manufacturing details so to simplify the process I thought
it best to copy that information from parent to child lot.
An append query or 2 seems like a good option but I need a
way for users to execute the queries perhaps from the form
when the child Lot gets created and postprocessed. Any
ideas on this or a better method to accomplish what I
need? Thanks for the help.

 

[Dwayne Shrum]

Sounds more like a process design rather than database
design where the database needs to mimic the process -
whichever process ends up being the most effective.

The only thing I came up with while reading your puzzle
was that I would favor the parent table having only the
attributes set that applied before the lot was split out -
plus I would have a Boolean field for setting a split flag.

Then, I would have a new intersection table that lists the
primary key of the parent and primary key of a child - one
set per row for each new child. So if a lot makes two new
sublots - then I would have two new lots in the main table
with this intersection table having two rows to link each
child back to momma.

I would also do as you hinted, by having the process that
creates the split be the moment in time that the new
records are created, parent data copied to each child, and
the entries in the interesection table added as a complete
transaction that could be rolled back.

This would permit a few good things.
1) Child lots would be in the main table
2) Parent lots could be divided into any number of child
lots
3) Child lots could become parent lots and have their own
offspring
4) The intersection table could always be consulted to
find any lineage
5) Any report/query could be limited to only those records
that have no offspring - keeping track of only final
processed lots

Just some ideas - hope they help.

 

[BG]

Thanks Wayne. I was thinking that the ParentID field for
the child would provide the link for lineage. Can you
explain the Split Flag. The primary key in both of my
tables is SampleID so I don't know how to setup the
intersection table. Bad design? I'm fairly new to this
level of complexity so I'm having trouble taking ideas to
code. Thanks for any details you can provide.

 

[Dwayne Shrum]

Your idea sounded like having a seperate table for the
split lots. In that new (children only) table having a
field for ParentID to link back to momma.

I was looking at flipping this concept inside out or
upside down - just the opposite, kind of.

Instead of a seperate table for the two types of lots
(those that are parents and those that are children) have
one table for all lots. Add a new field in that one table
of the yes/no (bit or Boolean) type where all unsplits are
no and splits are yes.

Create a new table that has only two fields for each
record. The two fields would be parent and child ID
numbers. This new table would just be a heritage table to
locate who begot whom.

The purpose of the true/false field in your main table is
so that you can query the table for only children or
parents, depending upon what you are wanting to know. If
every lot is divided into exactly two, your main table
would be 3 times larger than the incomming number of lots
entered. eg. inbound 20 lots, all 20 split, 40 new
children lots = total of 60 lots in the main table.

The second (lineage) table would then have (with this
example) 40 rows, or two rows per unique parent ID in the
main table.