List:MySQL on Win32« Previous MessageNext Message »
From:Michael Lang Date:July 31 1999 1:00pm
Subject:Re: Database structure
View as plain text  
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

Thread
Database structureDuke Normandin31 Jul
  • Re: Database structureMichael Lang31 Jul