5

I'm developing a database for Car Classifieds. I'm not sure how is the best way to design it. There are several features of cars like if it has 2 or 4 doors, if it is 4x4 or 4x2, engine capacity of 1.6 or 2.0.

All these features are particular for a car but they have to be limited to what that model has. So if someone is posting a bike, for example, it would not show the option in a drop-down for 4x4, or engines other than that model's.

I don't want to make this generic. It will only be for cars, so thinking about how it would be best for generic classifieds is not necessary.

What would be the best way to design this?

More Info:

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

5
  • How normalized do you want your design to be? Feb 19, 2013 at 12:56
  • I dont mind if its not fully normalized if it keep it simple and can manage this Model-Car features restrictions. Feb 19, 2013 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. Feb 19, 2013 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, 2013 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. Feb 21, 2013 at 14:45

1 Answer 1

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

5
  • 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. Feb 19, 2013 at 17:50
  • 4
    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, 2013 at 14:46
  • So is the car table populated from the attributes given from all of the previous tables? The other tables exist to populate inputs for dropdowns in a view and those attributes are populated into that final cars table? Also this is quite the comprehensive response. I hope I can get this good at designing db schemas
    – DeltaFlyer
    Jun 13, 2021 at 21:24
  • @mdoyle Also, shouldn't make_id (in the model table) and model_id (in the model year table) be foreign keys in those respective tables, referring to the primary keys in their original tables? Not trying to criticize for the sake of criticizing, I'm trying to understand how this all works with the hope of adapting it to a project I'm working on.
    – DeltaFlyer
    Jun 14, 2021 at 1:44
  • 1
    @DeltaFlyer Sorry for the late response, I haven't logged in for a while. Yes, the car table gets populated from the other tables. And you are right, all those PKs should be FKs when they show up in another table.
    – mdoyle
    Oct 27, 2021 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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