General Table Advice

[brharrii]

I'm building a product Information Database.

As part of the product data, I am required to indicate the master case used to package the product.

tblProductInfo
- ProductID
- ProdcutDesc
- MasterCase (FK to tblMasterCase)

tblMasterCase
- MasterCaseID
- MasterCaseDesc
- PalletConfiguration


For most products this setup works great. There are a handful (15-20) of products however, that have 2 piece master cases, usually a top(lid) and a bottom (case). To complicate the issue, one top may go on multiple bottoms, but never the other way arround.

some pretend examples to illustrate the point:

25 lb white cardboard bottom
goes with
Cardboard Lid

but also

25 lb Brown Cardboard Bottom
goes with
Cardboard Lid

50 lb styro Case
goes with
Styro Case Lid
(never with cardboard lid)

What is the best table / relationship structure to accomodate this data? I had considered these two options:

- many to many relationship between the two tables that allows for multiple mastercases to be selected per product.

- Setting up a 3rd table (tblMasterCaseBottom) that would only contain the bottom case and leaving the lid components in the tblMasterCase table

Thanks!

 

[YNWA]

Can you not have a MasterLidID and MasterBottomID in the Product table?

Allowing you to assign a bottom and/or a lid to your products?

Reading it, I am sort of thinking your confusing the relationship issue.

1 product will only have 1 lid and 1 bottom correct?

You won't be assigning multiples to the same product?

Maybe someone with more experience can read this better than me.

If your product can only have 1 lid and 1 bottom, I would have a table of Lids and a table of Bottoms. Then store the IDs of these in the Product table (meaning you would need an extra field in product tbl).

 

[brharrii]

thank you for your response.

to answer your quest, yes! I can do whatever I want with the tables I am, for all intents and purposes, the database admin on this project. Knowing that the way that I'm doing it is the correct way is the biggest issue for me.

So would you setup a different table for Lids and bottoms and then a completely different one for 1 piece master cases? or would you setup 2 tables and have bottom case mixed in with the master case table and the lids in a table of their own?

thanks!

 

[jdraw]

Another thought, since you say there are only 15-20 products, is to create the list (table) of valid lid/bottom combinations. Put them in a table of tblValidCombinations.

When selecting a Lid, or a bottom for a specific product, select the piece you want (lid or bottom) and the only valid other pieces to select from (bottom or lid) will be identified by your tblValidCombinations.
Haven't tried it, but just a thought.

 

[YNWA]

thank you for your response.

to answer your quest, yes! I can do whatever I want with the tables I am, for all intents and purposes, the database admin on this project. Knowing that the way that I'm doing it is the correct way is the biggest issue for me.

So would you setup a different table for Lids and bottoms and then a completely different one for 1 piece master cases? or would you setup 2 tables and have bottom case mixed in with the master case table and the lids in a table of their own?

thanks!

Depends on what the options are for each table.

If Lids data is different to bottoms and/or cases then yes.
If bottoms is different to lids and/or cases then yes
If cases is different to lids and/or bottoms then yes.

If any table will have the same info as another lookup table and that will ALWAYS be the case then you could have 1 table for lids/bottoms and 1 for cases. Or 1 for lids/cases and 1 for bottoms etc...

But I stress the ALWAYS part. If something changed down the line, your set up wouldn't be as efficient.

So might be better to have a table for each just to be sure.

CaseID, CaseDescription, Pallet
LidID, LidDescription, Pallet?
BottomID, BottomDescription, Pallet

Then in Product table have

ProductID
ProductDescription
CaseID (data type Number)
LidID (data type Number)
BottomID (data type Number)

Then your relationship would flow from CaseID product table to CaseID in Case_tbl and the same for other ID's.

Please note this set up is only possible if you will never have a product with more than 1 Case, Lid or Bottom.

 

[brharrii]

Thank you!