Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Storing Car Info - Design Suggestions

    I'm looking to store the following: Make, Model, Year, Sale Price, Serial Number.

    I know I will have the following table relationships:
    1. Table "Make" with a ONE to MANY relationship to table "Model"
    2. Table "Model with a ONE to MANY relationship to table "Car". Table "Car" contains "Sale Price" and "Serial Number"

    But where should I put year into there?

    Here are my three ideas:
    1. Put year it into the "Car" table as a data field.
    2. Have a "Year" table with a MANY to MANY relationship to a table named "Year_Model" which links tables "Year" and "Model" together.
    3. Have a "Year" table with a MANY to MANY relationship to a table named "Year_Make" which links tables "Year" and "Make" together.

    Any suggestions?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I reckon that year is an attribute specific to a car, not to a model of car; so I'd pick option 1 methinks
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i reckon that year is an attribute specific to a model, not to a specific car, so i'd put it into the model table

    ...if you even need a model table!

    why do you need separate tables for make and model? why not just one table for cars?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by r937
    why do you need separate tables for make and model?
    Lookups/heirachy?

    What car do you drive Rudy?
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does lookups/hierarchy have to do with the question?

    i drive a '69 vw beetle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by r937
    what does lookups/hierarchy have to do with the question?
    Nothing specifically, just assuming that with the modelling described that was what the separate tables were for.
    Quote Originally Posted by r937
    i drive a '69 vw beetle
    The year is specific to your car, not to the model.
    In this example, beetles are Type 1 Volkswagons. But you and I both know that year plays a very big part for the car. A 69 veedub is not the same as a 66 dub. (Am I right in thinking '67 was a big year of change for the beetle?)
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    The year is specific to your car, not to the model.
    quite wrong

    you said it yourself -- "A 69 veedub is not the same as a 66 dub."

    you are talking about models, not my car versus todd's car

    "a '69 beetle" is a model, not a specific car

    if you are talking about my car versus todd's car, well, duh, every single instance of a car is different

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

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    "A 69 veedub is not the same as a 66 dub."
    For such a classic car, yes this is true; but the model is nevertheless the same.

    Type 1 VW.

    Perhaps it's easier to make the distinction when looking at "newer" cars...
    If we take my piece of crap car into consideration:
    Make: Fiat
    Model: Uno mk II
    Year: 1992

    Uno mk I was produced between 1983 and 1989
    Uno mk II was produced between 1989 and 1995

    A '93 Uno mk II is not identical to a '92, but it is still the same model car - years are almost like versioning in respect that each year minor changes are made to style/functionality etc.
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, george, let's agree to drop this, you and i just aren't gonna agree

    awohld, why do you need separate tables for make and model? why not just one table for cars?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Agreed
    Cars(serialNumber, make, model, dateManufactured, dateRegistered, colour, salePrice, etc)

    But as mentioned before, perhaps the make and model tables are used for lookups?
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can "look up" all cars of a certain model without a "lookup" table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Correct.
    I guess I'm jumping the gun here - I'm thinking more in terms of the front end app - making sure users can't try sell a car whose make/model doesn't exist.

    Like my 1987 volkswagon mondeo.
    George
    Home | Blog

  13. #13
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    ignoring the one table vs multiple table issue...

    I think as far as what you asked, the question would be: Is year information associated with model or is it a piece of data that belongs to each car.

    If you want to have a Mustang for each year that they make one ('69 Mustang.... 2007 Mustang) then it is part of the model and each car will have that model/year linked to it. (you could have year ranges as george mentioned if there are no changes for a span of years)

    If you just want to indicate a model and store which year the car was then that is fine. This is probably a good way unless you plan to store model differences later that would mean a 1969 Mustang was different than a 2007 Mustang.

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by r937
    i drive a '69 vw beetle
    Good god - some thing I agree with Rudy on! I've owned my 1972 beetle for 20 years - it tends to be cosseted more than driven though.

    I agree with George though that the manufacturer should be in it's own table. I guess most car web site databases do have manufacturer in their own table as you normally get a list of manufacturers to choose from when searching for cars. It would also be useful if you wanted to store the contact details etc for the manufacturer.

    Mike

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    But we've both jumped the gun Mike - we have no idea how this system will be used - let alone whether it has a FE or not

    I love dubs - have done ever since we went on a camping holiday and got half way there when the darn thing broke down... We never did get to go camping.

    I haven't been to any of the shows in over 2 years now though - I was supposed to go to one up in Leeds 2 weeks back but something came up. One of our friends is a trader and she gets a load of free tickets to these things and offered them up! Shame I missed another good one

    Last one I went to was Bristol Volksfest 2005.
    George
    Home | Blog

Posting Permissions

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