Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2008
    Posts
    11

    Car dealership database

    Hi

    Im trying to create a auto car dealership database.
    This is what i have for the schema

    1.MANUFACTURER (Manuf_name, Make_id)
    2.MODELS (Model_id, Model_name, Make_id)
    3.OPTIONS (option_id, Option_name)
    4.CAR_OPTIONS (car_id, options_id)
    6.CAR_DESCRIPTION (car_id, color, stock_num, year, mileage, price, Vin_number,make_id, model_id)

    Primary keys underlined.


    I need advice is this a good design or is there any improvements that can be made?
    Also how can this be normalised if possible?
    Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Its well normalized.
    Can all options apply to any car? If not, you might want to define the options available for each model.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2008
    Posts
    11
    yes I was having a problem assigning several options to one car. I was thinking of assigning option 1 several options like a package

    e.g. option_id option_name
    1 Leather Interior, GPS, etc
    2 Cloth Interior, DVD player, etc.

    Is there any other way to do this?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You need to decide now whether your business model includes the concept of a "package" of options or not.
    That would be a real pain to implement later.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2008
    Posts
    11
    No it does include packages.. just what I consider to be options. such as leather interior, GPS, 2wd, 4wd, etc..
    Mainly it would be used for storing information about used cars.

    So what do you consider the best way for me to implement multiple options on one car.

    Lets say the car has, both leather interior and GPS

    Thank you for your response

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    4.CAR_OPTIONS (car_id, options_id)
    ...
    Primary keys underlined.
    Shouldn't the CAR_OPTIONS table have both car_id and options_id as the primary key? The idea is surely to list all the options for each car here and not to assign just one option to any car. Doing this means you just insert multiple records in the CAR_OPTIONS table, one record for each option that applies to that car.

    This structure is reasonably easy to work with but it can be a bit inefficient if you want to search for cars that have multiple options. Shouldn't matter to much as long as your used car garage doesn't have millions of cars.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I'd go back to the manufacturers iriginal broichure / reference point
    they are selling optional equipment.
    if the custoemr buys an option which ioncludes several items then thats one option, if a customer buiys several discrete options then they are discreter items

    eg
    Eg a car may have 3 grades of radio base, option1, option2
    option 1 may be a cd/car radio
    option 2 may b e a DVD/Car radio with Navigation software and a bluetooth fitting
    option 1 may also include (asy a MP3 socket) which may be a separate option for the other specifications.

    option lists are uniwue to a manufacturer, a model of car, and in many cases a year of car (ie the MP3 socket may be availabl3 from 2002 on)
    the Mp3 socket may be standard on a certain model trim

    what you could do is build a template for each trim level and add any additional items as options on a per individual car on the forecourt. so when you retrieve the fit of any specific model and trim (you suck across the standard fit for that trim level, and any specific options for this model). If this option is for information ONLY then you could consider EAV for the options
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2008
    Posts
    11
    Ok, I will make the option_id a PK of the CAR_OPTIONS table.

    The problem I have is when entering data into the option_name (OPTIONS table) I think it should be like:

    option_id-----------------------------option_name
    1---------------------------------------2wd
    2 --------------------------------------Manual transmission

    How would I assign both of these options to a car

    Car_id ---------------------Option_id
    1-------------------------- 1,2 (???)

    Thats the problem I have. If its a primary key I think It can only be 1 unique value?
    Last edited by l_starter_l; 11-26-08 at 14:51.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The primary key will be on both fields ie :
    Code:
    create table CAR_OPTIONS (
       car_id           int, 
       options_id    int,
       PRIMARY KEY ( car_id, options_id )
    )
    You'd then insert as many records as the car (give it id 1) has options. We'll say 2whl drive is 101 and manual trans is 102 :
    Code:
    insert CAR_OPTIONS values ( 1, 101 );
    insert CAR_OPTIONS values ( 1, 102 );
    Are these really options on cars in the US? what would you get if you didn't order these options?

  10. #10
    Join Date
    Nov 2008
    Posts
    11
    Yea I suppose they are options..or more like choices..

    You can choose between 2wd or 4wd
    Maunal or automatic trans.

    Does int work in ms access or should i use varchar?

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Yea I suppose they are options..or more like choices..

    You can choose between 2wd or 4wd
    Maunal or automatic trans.
    At the moment your structure just allows you to tick a number of random options - I can't see anything to say that an option has a choice of 2wd or 4wd - ie at the moment you could choose neither or both. You might need to think about how to represent this. You could also just assume that all cars have 2wd and that 4wd is the only option.

    It's probably unnecessary but you could expand your options table so you could set options on a particular make or model - let's say that the Hummer has 4wd as standard on all it's models. You could then expand this further to have a negative condition ie all Hummers get 4wd except model x. Y could could also go further to do the defaults across all makes this way as well.

    If I were you I'd go with the most simple database design that works but also keep in mind how you could expand it to meet future requirements. You also might want to add foreign keys between the different tables.

    Does int work in ms access or should i use varchar?
    I've no idea - never used it.

  12. #12
    Join Date
    Nov 2008
    Posts
    11
    I have foreign keys

    Foreign Keys
    1. MODELS: Make_id with MANUFACTURER
    2. CAR: Make_id with MANUFACTURER (Make_id); Model_id with MODELS (Model_id)
    3. CAR_OPTIONS: option_id with OPTIONS; car_id with CAR
    4. CAR: make_id with MANUFACTURER; model_id with MODELS
    5. CAR_DESCRIPTION: car_id with CAR

    I assumed if I assigned 1 option to a specific car that all I needed. I dont have to put both... so if I put

    insert CAR_OPTIONS values ( 1, 101 );
    insert CAR_OPTIONS values ( 2, 102 );


    that would mean car_id 1 has 2wd..
    car 2 has 4wd
    where 101=2wd
    102=4wd

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    1. MODELS: Make_id with MANUFACTURER
    2. CAR: Make_id with MANUFACTURER (Make_id); Model_id with MODELS (Model_id)
    3. CAR_OPTIONS: option_id with OPTIONS; car_id with CAR
    4. CAR: make_id with MANUFACTURER; model_id with MODELS
    5. CAR_DESCRIPTION: car_id with CAR
    Where has the CAR table come from? what does it hold that's not in the CAR_DESCRIPTION table?

    I assumed if I assigned 1 option to a specific car that all I needed. I dont have to put both.
    What if you want to give a car the options for both automatic transmission and 4wd? Then ask yourself how you'd stop the user from giving both the options 4wd and 2wd (assuming they were both options).

    Does int work in ms access or should i use varchar?
    I did a quick search on Google and came up with this page

  14. #14
    Join Date
    Nov 2008
    Posts
    11
    Yea the I merged the CAR table with CAR_DESCRIPTION because I thought that table was unnecessary.
    ..user from giving both the options 4wd and 2wd (assuming they were both options).
    True I didn't think about that..

  15. #15
    Join Date
    Nov 2008
    Posts
    11

    help!

    My design is:

    However I can enter data in the car table, there is an error.
    Is there a key problem anywhere?
    Maybe between the CAR and CAR_OPTIONS tables?


    Create table MANUFACTURER(
    Manuf_name char(16) Not Null,
    Manufacture_id Varchar(2) Not Null,
    Primary Key (Manufacture_id));

    Create table MODELS(
    Model_name varchar(16) Not Null,
    Model_id Varchar(2) Not Null,
    Manufacture_id Varchar(2) Not Null,
    Primary Key (Model_id),
    FOREIGN Key (Manufacture_id) REFERENCES MANUFACTURER);

    Create table OPTIONS(
    option_name varchar(16) Not Null,
    option_id Varchar(2) Not Null,
    Primary Key (option_id));

    Create table CAR(
    year varchar(4) Not Null,
    car_id Varchar(3) Not Null,
    color char (20) Not Null,
    Manufacture_id Varchar(2) Not Null,
    Model_id Varchar(2) Not Null,
    stock_num varchar (3) Not Null,
    price float Not Null,
    mileage varchar (10) Not Null,
    Vin_num varchar (7) Not Null,
    Primary Key (Vin_num),
    Foreign Key (car_id) references CAR_OPTIONS,
    Foreign Key (Manufacture_id) references MANUFACTURER,
    Foreign Key (model_id) references MODELS);

    Create table CAR_OPTIONS (
    car_id Varchar(3) Not Null,
    option_id Varchar(3) Not Null,
    Primary Key (car_id),
    Foreign Key (option_id) references OPTIONS);

Posting Permissions

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