Relationships

[Mike Revis]

Hi group,

I have 4 tables related one to many in the order of tblOrder 1/m
tblMasterpack 1/m tblPackage 1/m tblPart.

This all goes along fine until I don't have a masterpack.

With no data in the masterpack table it breaks the chain and the order can't
find the package.

Any ideas on how to make the tblMasterpack optional?

Thanks for your ideas.

Mike

 

[Ken Snell]

I'm not sure I understand your setup. Are you saying that your table
structure is
tblOrder 1:many tblMasterpack
tblMasterpack 1:many tblPackage
tblPackage 1:many tblPart

Or is it
tblOrder 1:many tblMasterpack
tblOrder 1:many tblPackage
tblOrder 1:many tblPart

Need more clarification before we can provide an answer. If you're saying
that the first list is what you have, I'm not understanding how you would
"have" any records in the chld tables if there is no "linking" record in
tblMasterpack table.

 

[RSGinCA]

I'm a database designer and I was ready to ask you all kinds of questions so
that I could actually fully understand your data, but April 15th is rapidly
approaching and Uncle Sam demands my attention...

However, let me just ask... What is a "masterpack"? So, the design of your
database was based on the assumption that any order has one or more
masterpacks. And each package is part of a masterpack. But you've discovered
that that isn't quite true. Why? How does that happen?

OK, I like to fully understand the data but one appoach to solving this kind of
problem involves doing a little tweaking here and there... I really know little
about the actual design of your tables, but I'll make some assumptions and make
up a data name. How about simply adding an OrderID field to your tblPackage
table. That will add a direct 1/m relationship between tblOrder and tblPackage
while retaining all of your existing information and relationships. I suspect
that that might work. However, like Ken Snell said, without actually seeing
the design/layout of your tables we need to make a lot of assumptions in order
to make a suggestion.

Rick

 

[Rolls]

It appears that tblMasterpack is optional. tblOrder may be related to it or
it may not. The key question then becomes whether tblPackage is in and way
dependent on tblMasterpack. If not, then tblMasterpack is related only to
tblOrder:

tblOrder < tblMasterpack
tblOrder < tblPackage < Part

instead of:

yblOrder < tblMasterpack < tblPackage < tblPart.

 

[John Vinson]

Hi group,

I have 4 tables related one to many in the order of tblOrder 1/m
tblMasterpack 1/m tblPackage 1/m tblPart.

This all goes along fine until I don't have a masterpack.

With no data in the masterpack table it breaks the chain and the order can't
find the package.

Any ideas on how to make the tblMasterpack optional?

I gather that there are *some* packages which are parts of an order,
but NOT part of a masterpack - correct?

One kludgy solution would be to make up "minimasterpacks" - records
in the Masterpack table which contain only one package.

If this is unreasonable, you might instead want to put an OrderID
field in the Package table so that the relationships run

Order -> Package -> Part

and have the Masterpack table *separately* related to Packages. You
could then have a query

Order -> Package <- Masterpack

to find the masterpacks which are included in the order.

 

[Mike Revis]

Thank you all for your responses. I had to do a little thinking about them.

The tables I have set up are tblJob, tblPart, tblPackage and tblMasterpack.

tblJob has about 15 fields of data about the job as a whole.
tblPart has about 12 fields of data about the part.
tblPackage has about 8 fields of data about the package.
tblMasterpack has about 6 fields of data about the masterpack.

The relationship I have set up is
tblJob 1/m tblMasterpack
tblMasterpack 1/m tblPackage
tblPackage 1/m tblPart
Just to explain a little. We are a packaging company so the Orders analogy
may not be appropriate.

A customer can bring us 1 of part A. Part A goes into a package. Easy
enough.
or..
He could have 100 of Part A. Each Part A goes into an individual package.
Then 10 individual packages of Part A go into 10 masterpacks. Or all 100
packages remain individual.
or..
We get 1 of Part A and 1 of Part B. Part A goes in one package. Part B goes
in one package. Then the two packages go into 1 masterpack. Or not.
or..
We get 100 of Part A and 100 of Part B. 1 each of Part A and 1 each of Part
B go in one package. 10 each of the Part A and Part B packages go into 10
masterpacks. Or not.

We could have an infinite number of parts going into any possible
combination of packages and masterpacks.

The possible combinations are limitless. I think?

I apologize for the long winded explanation and as always thank you all for
your help.

Mike

 

[John Vinson]

We could have an infinite number of parts going into any possible
combination of packages and masterpacks.

The possible combinations are limitless. I think?

I think if you make the one (again, slightly kludgy) convention that
every package must be in a Masterpack, even if that Masterpack
contains only one package, your datastructure will work fine.