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

Its well normalized.
Can all options apply to any car? If not, you might want to define the options available for each model.

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?

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.

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

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.

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

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?

The primary key will be on both fields ie :
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 :
Are these really options on cars in the US? what would you get if you didn't order these options?

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?

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.

I've no idea - never used it.

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

Where has the CAR table come from? what does it hold that's not in the CAR_DESCRIPTION table?

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).

I did a quick search on Google and came up with this page

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..

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);