List:MySQL on Win32« Previous MessageNext Message »
From:Michael Lang Date:July 31 1999 1:00pm
Subject:Re: Database structure
View as plain text  

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

To answer your question, I'd break down each hierarchy into its own table:

ID: integer
Name: string

ID   Name
1    Ford
2    Toyota
3    Pontiac
4    Nissan

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

ID: integer
SKU: string
Name: string

ID  SKU         Name
1   a8s78d790s  GM Oil Filter
2   a8d90s0230  Ford Oil Filter

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.


Database structureDuke Normandin31 Jul
  • Re: Database structureMichael Lang31 Jul