Duke,
The general goal to elminate redundancy of all types. The formal process
you're looking for is called normalization and there are very specific
rules at each stage of analysis, 1st Normal Form, 2nd Normal Form, and so
on. Generally, 3rd Normal Form is as far as implementors take the process
in practice. You'll want to read up and study the process. Surprisingly,
one of the better common layman's book to read on the subject is Oracle for
Dummies. But, if you're curious now, search the web for "Normal Form" or
"1NF", "2NF", "3NF". I did a quick search and came up with the following
site:
http://www.agsm.ucr.edu/mis/mgt230/Lecture5/index.html
To answer your question, I'd break down each hierarchy into its own table:
Manufacturers
-------------
ID: integer
Name: string
ID Name
---------
1 Ford
2 Toyota
3 Pontiac
4 Nissan
Models
------
ID: integer
MfgID: integer
Model: string
ID MfgID Model
-----------------------
1 1 Taurus
2 1 Escort
3 1 Ranger
4 2 Camry
5 2 Corolla
6 3 Grand Am
7 3 Grand Prix
8 3 Trans Am
Parts
-------
ID: integer
SKU: string
Name: string
ID SKU Name
---------------------
1 a8s78d790s GM Oil Filter
2 a8d90s0230 Ford Oil Filter
Parts_To_Models
---------------
SKU ID: integer
Model ID: string
Year: integer
SKU ID Model ID Year
------------------------
1 6 1999
1 6 1998
1 6 1997
1 7 1999
1 7 1998
2 1 1999
2 2 1999
The table above is probably going to contain by far the most records of all
your tables so the structure above going to be substantially more efficient
to store, retrieve, and search than the following table:
Mfg Model Year SKU Part Name
-----------------------------------------------------
Pontiac Grand Am 1999 a8s78d790s GM Oil Filter
Pontiac Grand Am 1998 a8s78d790s GM Oil Filter
Pontiac Grand Am 1997 a8s78d790s GM Oil Filter
Pontiac Grand Prix 1999 a8s78d790s GM Oil Filter
Pontiac Grand Prix 1998 a8s78d790s GM Oil Filter
Ford Taurus 1999 a8d90s0230 Ford Oil Filter
Ford Escort 1999 a8d90s0230 Ford Oil Filter
Hope this helps.
Michael
