Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Im developing a Car Classifieds but im not sure of how is the best way to design something, there are some features of cars like if it have 2,4 doors, if it is 4x4 or 4x2, engine 1.6, 2.0.

All this features are particular for a car but they have to be limited to what that mode have, so each time someone is posting a bike for example it would not show the option in a drop-down for 4x4, or engines than that model.

I don't want to make this generic, it would only work for cars, so thinking in how it would be best for generic classifieds its not necessarily.

What would be the best way to design this?

More Info:

My problem is how to design the relation between a Car Model and a actual car, for example the Year of a Model could go from 1990-2000, when someone is going add a Car of that model i only want them to be able to choose from the options that model have. How do i store this in a database?

share|improve this question
    
How normalized do you want your design to be? –  Mr.Brownstone Feb 19 '13 at 12:56
    
I dont mind if its not fully normalized if it keep it simple and can manage this Model-Car features restrictions. –  Luis Tellez Feb 19 '13 at 13:14
2  
If anyone does not feel like this is a good question at lest let me know why, and dont just down vote plz. –  Luis Tellez Feb 19 '13 at 16:18
    
I suppose the down-votes come from colleagues that would have appreciated the question more if you'd already provided with an example with your already done work (even if it's wrong, it is a start point). But you're lucky to receive a useful answer nonetheless :-). –  Marian Feb 21 '13 at 14:35
    
Well i could had provided more information and the 2 approaches i had tough but they were not requested and i was trying to get new ideas. And thats why i asked to tell me what was the problem instead of just down vote so i could do something about it. –  Luis Tellez Feb 21 '13 at 14:45

1 Answer 1

up vote 8 down vote accepted
+50

Perhaps something like this:

CREATE TABLE make (
    make_id INT NOT NULL,
    make_name VARCHAR(25),
    PRIMARY KEY(make_id);
);

CREATE TABLE model (
    model_id NOT NULL,
    make_id NOT NULL,
    model_name VARCHAR(25),
    PRIMARY KEY(model_id)
);

CREATE TABLE model_year (
    model_id INT NOT NULL,
    year_num INT NOT NULL,
    PRIMARY KEY (model_id, year_num)
);

CREATE TABLE transmission (
    transmission_id INT NOT NULL,
    transmission_descrip VARCHAR(15),
    PRIMARY KEY (transmission_id)
);

CREATE TABLE drive (
    drive_id INT NOT NULL,
    drive_descrip VARCHAR(10),
    PRIMARY KEY (drive_id)
);

INSERT INTO transmission (transmission_id, transmission_descrip)
VALUES (1, 'Auto'), (2, '4-speed manual'), (3, '5-speed manual');

INSERT INTO drive (drive_id, drive_descrip)
VALUES (1, '2WD Front'), (2, '2WD Rear'), (3, '4WD'), (4, 'AWD');

To allow someone to add a 2005 Toyota Camry, first you need to make sure Toyota is in the make table, Camry is in the model table, and 2005 is available for the Camry:

INSERT INTO make (make_id, make_name)
VALUES (1, 'Toyota');

INSERT INTO model (model_id, model_name)
VALUES (1, 1, 'Camry');

INSERT INTO model_year (model_id, year_num)
VALUES (1, 2005);

We keep certain data on car buyers at my job, and in selecting the car type (either currently owned or looking to buy), we have them select the year, then make, then model and options from drop-downs. Each drop-down is populated on the fly based on previous answers. First the year:

SELECT DISTINCT year_num FROM model_year;

Then the make (you can't pick Pontiac for 2013):

SELECT DISTINCT m.make_id
FROM make m
INNER JOIN model mo ON mo.make_id = m.make_id
INNER JOIN model_year my ON my.model_id = mo.model_id
WHERE my.year_num = @some_year_variable;

Then the model drop-down gets populated based on:

SELECT DISTINCT model_id
FROM make m
INNER JOIN model mo ON mo.make_id = m.make_id
INNER JOIN model_year my ON my.model_id = mo.model_id
WHERE my.year_num = @some_year_variable
    AND ma.make_id = @some_make_variable;

You get the idea. As far as constraining other options for a model, there are a few options, based on how strict you want to be, how much time you want to spend managing it, and how much you know about the auto industry.

Since all cars have a drive type and transmission, you might want to make those separate tables, like:

CREATE TABLE model_transmission (
    model_id INT NOT NULL,
    year_num INT NOT NULL, -- include this to restrict tranny based on some years 
                           -- certain models had different trannies offered   
    transmission_id INT NOT NULL,
    PRIMARY KEY (model_id, transmission_id) -- include year_num in PK if it is in the
                                            -- table
);

CREATE TABLE model_drive (
    model_id INT NOT NULL,
    year_num INT NOT NULL, -- again, if you are up to this level of control
    drive_id INT NOT NULL,
    PRIMARY KEY (model_id, drive_id) --include year_num in PK if it is in the table
);

Or you could use an option table and a model_option table, like this:

CREATE TABLE option (
    option_id INT NOT NULL,
    option_descrip VARCHAR(25),
    PRIMARY KEY (option_id)
);

INSERT INTO option (option_id, option_descrip)
VALUES (1, 'Transmission'), (2, 'Drive'), (3, 'Doors');

CREATE TABLE model_option (
    model_id INT NOT NULL,
    option_id INT NOT NULL,
    option_value VARCHAR(15),
    PRIMARY KEY (model_id, option_id) -- include year_num as above if desired
);

INSERT INTO model_option (model_id, option_id, option_value)
VALUES (1, 1, '1'), (1, 2, '1'), (1, 3, '4'); -- All Camrys now have automatic,
                                              -- front-wheel drive, and 4-door options
                                              -- available

I prefer the first approach as you can see the problem with referential integrity inherent in the second, plus it makes it more awkward to restrict available options. The table for listed cars might be like this:

CREATE TABLE car (
    listing_id INT NOT NULL,
    year_num INT NOT NULL,
    model_id INT NOT NULL,
    transmission_id INT NOT NULL,
    drive_id INT NOT NULL,
    doors INT NOT NULL, -- might want varchar to include 'hatchback' or other options
    mileage INT,
    customer_id INT NOT NULL,
    PRIMARY KEY (listing_id)
);

If this doesn't address a particular feature you believe you need just add a comment.

share|improve this answer
    
Thanks a lot Mdoyle, this is what i was going for, i was trying to decide between the 2 options, and as i dont need it to be Generic, i just want it for cars im going for the first approach. –  Luis Tellez Feb 19 '13 at 17:50
3  
If this is what you were looking for, perhaps you might accept it as the answer. Otherwise I'd be more than happy to expound on it if more is needed. –  mdoyle Feb 20 '13 at 14:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.