Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hey!
I need to create the structure of the database of a vehicle marketplace website. I'm thinking of something like
Categories
- id
- name (car, truck etc...)
Brands
- id
- category_id
- name
Models
- id
- brand_id
- name
- period_of_creation (e.g. 2010-2015)
- facelift (boolean)
- doors
Models Variants
- id
- model_id
- fuel
- engine (e.g. 1.9 TDI)
- hp (e.g. 90)
Listings
- id
- user_id
- brand_id
- model_id
- model_variant_id
Category hasMany Brands
Brand belongsTo a Category
Brand hasMany Models
Model belongsTo a Brand
Model hasMany Model Variants
Model Variant belongsTo a Model
I did that for a few minutes and it's not even very thoughtful. What do you think about it? I'm looking for your suggestions!
@laralex You seem to be pretty much there. Vehicles usually tend to be described in terms of “make” (Ford, Audi, etc) and “model” (Focus, Quattro, etc). Obviously you’ll need some way of identifying models from different years, too. You could either store multiple rows for each year’s model, or have some sort of variant table like you do now.
@martinbean I would be really satisfied if there is a lists of make-model-year-specifications that I can either copy locally or retrieve every time on page load. Is there something like this existing? It will be also great if this list is updating periodically. Because it will be quite painful to insert all the brand-model-year specifications for each car. There are thousands of specifications for each car. Another approach will be an API but sadly I'm pretty sure there is no API for cars.
@martinbean Or I can use a VIN Decoder API and retrieve the car information from there? Is that a good approach? This will save users ton of time while listing their car for sale. Look at the Response example in JSON section from the link I shared.
@Laralex I hope this helps: https://www.teoalida.com/cardatabase/
https://car2db.com/, you can download a mini sample of their database that might give you some ideas.
If you make the VIN required a decoder could help, if it is optional typing a VIN is probably more work than selecting a car model.
Also consider not making your model variants too specific if you do not have that many car models. It depends on your requirements but imagine you have 20 Ford Focus models, does it really matter that you specify a specific model variant? A user would not mind scrolling through 20 ford focus models to find the model they are looking for. But this is something that completely depends on your (client) requirements.
Please or to participate in this conversation.