Database Design - Am I doing it right?

[brharrii]

I've taken appart and re-assembled my database several times simply as a result of my limited experience and understanding of database systems. This time before I invest several hours of work into it again, I wanted to run some ideas past the community and see if anyone could let me know if I'm on the right track, or if I need to change some things up. Here goes:

My database keeps track of everything produced in our facility in a main table called tblProductInfo. Each product has several packaging elements to consider:

Packaging
- Film
- Insert
- Liner
- RetailCase


- MasterCase
- MasterCaseBottom
- Component
- Divider


Each product may have one or more package types associated with it. For example a product may be packed into a 35 mm film, put into a 6 lb retail case, and then put into a 30 lb mastercase with a mastercase bottom that uses a t shaped divider. Another product may go directly into a 15 lb master case.

The challenge is that some package types have additional attributes that ONLY apply to that package type. For example, All package types have item numbers, but each Master Case will also have an associated Pallet configuration, a case weight, and a tare weight. Each Film will have a bottom film, a bottom film ID, a film heating tempurature, a tare weight, and a pocket count. Each Retail case will have a tare weight, a case weight, and a unit count that go with it.

My question is, what is the best way to organize this information? Should I dump all package types into a single table and setup Many-to-Many relationships with my main table? Or would it be better to create unique fields on my main table for each package type and string multiple relationships from my main table into a single Packaging table? or should I be breaking my packaging table into smaller tables? (One for Master Case, one for Retail Case, one for Film, and one for Others)

Thanks in advance for any suggestions.

[brharrii]

I'm moving this thread to "General Access Questions"