I'm currently planning the database structure of a used car's advertisements site. Each advert contains information about a vehicle, and a vehicle can be advertised multiple times over its lifespan (granted, quite unlikely). I'm unsure which approach to take – a single table, or two separate relational tables, each has their pros and cons.
Schema 1
Adverts Table:
-------------------
id int
make varchar(255),
model varchar(255),
year int,
transmission varchar(255),
fuel_type varchar(255),
body_type varchar(255),
engine_size int,
colour varchar(255),
doors int,
location varchar(255),
price int,
owners int,
mileage int,
description longtext
Schema 2
Adverts Table
-------------------
id int
location varchar(255),
price int,
owners int,
mileage int,
description longtext
vehicle_id int
Vehicles Table
-------------------
id int
make varchar(255),
model varchar(255),
year int,
transmission varchar(255),
fuel_type varchar(255),
body_type varchar(255),
engine_size int,
colour varchar(255),
doors int
Personally, I'm thinking taking the second approach will benefit in the future, but it's proving tricky to work with. I'm using Laravel, a PHP framework. Because most of the "search" options are applied to the vehicle (parent), rather than the advert (child), a lot more code is required, which is starting to seem counter-intuitive. Schema 1 is far simpler to work.
Am I missing any extensive benefits of going with schema 2? In a nutshell, the first approach requires much less code, but isn't relational. The second approach is relational, but it's requiring far more code (and time), reducing the brevity of my adverts controller in particular, which is actually querying the vehicle's attributes more than the advert's. It's also making "sorting" quite tricky, as user's can sort by "make", "model", "location", "price", etc – attributes which jump between model.
Best Answer
The decision between choosing a relational model over a de-normalized model is typically one of scale and the type of database operations that you anticipate most likely to occur.
A relational database is typically easier to query on and is more efficient for transactional heavy applications while a denormalized schema will be more appropriate if you plan on storing a large warehouse of data that you plan to run analytics or reports on.
If time is your bigger concern and you don't believe this site will have much traffic over the long term then by all means choose schema 1, but I recommend documenting the reasoning behind your eventual decision in the chance that someone else might be maintaining your work in the future and may be struggling with a feature that is at odds with your schema decision.
Myself, I would take the time to make it as relational as possible, but I am a perfectionist.
ProTip: Consider adding VIN number as a natural key to the vehicle table. It will help you identify individual vehicles and it relates to a real easily identifiable attribute of a real vehicle.