CODEWITHSUNDEEP
databasedatabase-design
Luis Tellez
Luis Tellez

Reputation: 2983

Database Design Cars-Models / Car Classifieds

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?

Upvotes: 1

Views: 1816

Answers (2)

Neil McGuigan
Neil McGuigan

Reputation: 48256

You need to differentiate between a car model instance ("my car vin xxx") and a car model specification ("2009 Mazda 3"). In general a model spec has a model number and a model instance has a serial number (the VIN in this case).

You also need to differentiate between the available options for a model spec and the actual installed options for a model instance.

/* the model specifications: */
vehicle_model
id
model_name
from_year
to_year (nullable)
manufacturer_id


/* engine specifications */
engine
id
name (ex. "Cummings Turbo Diesel")
...


/* available engines for a model. you would use this table to show only the appropriate engines for a given model spec */
vehicle_model_engine
model_id
engine_id


/* vehicle instances: */
vehicle
id
vin
model_id FK vehicle_model
engine_id FK engine

You could either put a multi-column foreign key from vehicle(model_id, engine_id) to vehicle_model_engine(model_id, engine_id), or just put a single key to engine(id) and use application logic to check before saving.

Upvotes: 2

ryan1234
ryan1234

Reputation: 7275

I work at an automotive company, and I would recommend starting with year, make, model and trim. All cars have these values. You can hardcode those into a database structure.

Beyond that, just make an attribute table that will have those additional attributes.

Don't bother trying to make fields specifically for engine, number of doors, etc. You'd be surprised that some cars have more than one engine (!).

Tables for YMMT and attributes:




Vehicle



vehicle_id int not null (auto increment),
year int,
make varchar(100),
model varchar(100),
trim varchar(100)



Vehicle_Attribute



vehicle_id int,
attribute_name varchar(100),
attribute_value varchar(100)

Upvotes: 0

Related Questions