Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    26

    Vehicle table normalization

    I see two scenarios, but not sure which is proper:

    Scenario 1:

    Table: Years
    ID
    Year

    Table: Makes
    ID
    Make

    Table: Models
    ID
    Model

    Table: Series
    ID
    Series

    Table: Vehicles
    ID
    YearID
    MakeID
    ModelID
    SeriesID


    Scenario 2:


    Table: Years
    ID
    Year

    Table: Makes
    ID
    YearID
    Make

    Table: Models
    ID
    YearID
    MakeID
    Model

    Table: Vehicle
    ID
    YearID
    MakeID
    ModelID
    Series

    The goals is to have people narrow down their vehicle selection by starting with year, then make, the model then series. Thoughts?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    well it's be a cracking start to make an effort at your coursework before posting here
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2006
    Posts
    26
    What do you mean? I am not in school. I am just trying to learn on my own...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Eiolon View Post
    Table: Years
    ID
    Year
    this always makes me plotz with laughter

    do yourself a favour and stop applying ids to everything
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2006
    Posts
    26
    Sigh...

    Okay, like I said, I've just been trying to learn on my own. All I have are Internet tutorials and books telling me to assign unique ID's to everything. I'd love to learn the "proper" way, whichever that is, since what I keep reading doesn't seem to be it.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Eiolon View Post
    ... tutorials and books telling me to assign unique ID's to everything.
    whatever you've been reading, it's wrong

    one does ~not~ assign ids to everything willy-nilly

    you should assign a surrogate key (for that's what it's called) only when a suitable natural key does not exist

    suitable often means that the key can be used in other tables as a foreign key without becoming unwieldy

    what's a natural key? any column, or combination of columns, that already exist within the data, that uniquely identify each row

    more than this, i cannot tell you now, without turning this thread into a tutorial, which i'm not prepared to do

    do some research on natural vs surrogate key -- it's a contentious issue, to be sure, but anyone who says you should apply a surrogate key to every table is deluded
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2006
    Posts
    26
    Thank you. I have been reading SQL books for awhile and surprisingly its the first time I have heard of the two key types. I have heard primary and foreign but that's it. I will read more into it.

    By the way, your book Simply SQL, is it written for beginners such as myself, or for more experienced SQL users?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    definitely for beginners

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •