Results 1 to 12 of 12

Thread: Car Dealership

  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Car Dealership

    Hello everyone,

    I have to design a website for a car dealer. This dealer sells both new and used cars. So I have to design a database that manages the dealer's inventory. People can sell their car to this dealer through a form on the website.

    I wanted to check with you guys that my database model is right before I start programming:

    TABLE car_make (make_id, make_name)

    TABLE car_model (model_id, make_id, model_name)

    TABLE car (car_id, make_id, model_id, stockno, year, price, type, interior_color, exterior_color, fuel_type, transmission, doors, used_or_new, state_id)
    [state_id means whether the car belongs to the dealer or is a potential car from a seller]

    TABLE features (feature_id, feature_name)

    TABLE car_features (car_id, feature_id)

    TABLE pictures (picture_id, car_id, picture_small, picture_large)

    TABLE people (people_id, name, phone, address, email)

    TABLE people_car (car_id, people_id, buyer_or_seller)
    [people cold be the buyers (clients) or sellers (traders)]

    Now there will be a form in the website through which any website visitor may enter information about the car they may want to sell to this dealer. The form contains exactly the same fields as in the TABLE "car" + the TABLE features. Upon submittal of the form, I don't know whether I should include that form info directly into the database or create a new table that manages trades??

    Any help would be greatly appreciated.

    Thanks much

  2. #2
    Join Date
    Aug 2003
    Location
    Toronto, Ontario, Canada
    Posts
    203
    I know different states/provinces have different emissions standards, you may want to include which emission standards the car meets:

    emissions_id state_id year

    (I include year because standards change from year to year)

    You may also want to check out the forms on a number of different sites and see what they include.
    When it rains, it pours.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would probably not have the makes and models table

    i would not put trades in a separate table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2008
    Posts
    3
    if I do not include make and model tables, how could anyone search for a car of a particular make and model?

    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    like this:
    Code:
    select ...
      from cars
     where make ='ford' and model ='pinto'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2008
    Posts
    3
    thanks for your prompt reply.... you're saying that instead of creating 2 tables, I should just include 2 fields (car_make, car_model) in the car table, right?
    Thanks

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    While I completely understand Rudy's rationale, I have to ask him a question... How do you validate that a user has entered a correct value for make? I've never heard of "foord"

    Personally I like (and argueably overuse) "lookup tables" which use foreign key constaints. "Ford" is a perfectly god natural key to use; but having a "master make" table allows RI to be enforced.

    Whatcha think Rude-meister?
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    A lot of these topics were discussed here about storing car info.

    I prefer a lookup table for the car makes but suspect it will be difficult to do the same on models. You might want to have very loose matching on the models ie converting to lower case, and using like or the "sounds like" functions.

    I notice you're big on id's in all your tables (status, features, make) - it may be a personal preference but I find it difficult difficult dealing with meaningless numbers when readable codes are easier to understand i.e.
    Code:
    if status in ( 4,13,-7) then ...
    or
    if status in ( 'IN STOCK', 'LOOKING FOR', 'SELLING' ) then

    With complicated details like the emissions it may be better just ignoring that data all together and leave it to the salesman. I know I get flustered when I'm asked to fill in anything complicated like that into a form so coding for those details may, apart from being difficult, be counter productive.

    Mike
    Last edited by mike_bike_kite; 01-30-08 at 18:48.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    While I completely understand Rudy's rationale, I have to ask him a question... How do you validate that a user has entered a correct value for make? I've never heard of "foord"
    to reply to this question, it is necessary to assume a priori that one wants to validate the value

    and i like how you phrased it ... you've never head of "foord"

    but are you certain that you can "pre-load" all the makes and models that you're going to get?

    suppose your web site is a site where people can list their cars for sale, and other people can search the cars for sale, and you've got a revenue stream from that

    are you seriously going to reject every make and model that you haven't heard of? wouldn't that be new-business-hostile...

    okay, with that question out of the way, we can move on to validating, i.e. enforcing

    well, george, i'm surprised you're asking the question

    the answer is: with foreign keys!

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

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by r937
    to reply to this question, it is necessary to assume a priori that one wants to validate the value
    Touché my Canadian friend, touché
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would typically do what George and Mike have suggested. I consider the lookup table in this instance to be more for the benefit of the application than the database - I don't consider it a data integrity issue but a data correctness issue. I would not restrict the values to only those in the table at the time of entry. If a user enters "foord" then they would be politely informed that we don't have a record of that manufacturer, but we have some similar such as.... "Are you sure this is your manufacturer?" If they say yes then add the value to the lookup table.

    This just reduces the likelihood of typo errors. On the other hand, it requires more code in the app however it is the sort of thing you can write generic code for.

    Just me $0.2c. Feel free to refund me if ya don't like it

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Of course, if the OP had a requirement to record information about manufacturers then this becomes a totally different discussion....

Posting Permissions

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