I kindly ask you for advice, if I am correct with my DB design. I am writing an app in Django (it has its own ORM), which will use PostgreSQL.
My expectation is, that I will specify:
- Type (example: Automobile, Motorcycle, Boat, …)
- Make (example: Honda, BMW, Audi, …)
- Model (example: CBR1000, A6, A8, 535i, …)
- Vehicle – all other info, like VIN, price, …
So I think that the best way is to have Makes unique. I don't know, what to do with a Type. I think that the best way is to have a foreign key of a Type in a Model – because for example Honda can produce Automobiles and also Motorcycles. So by Model we can found, what vehicle type is it.
I want to make a web form filter (some kind of three linked dropdowns), where the user will select a Type, then it will filter out all Makes for that selected type. Then in another dropdown, he will select a Make and in the third dropdown, there will be shown all Models, which is available in that selection.
Something like a Copart has – Vehicle Finder on the right side of web page https://www.copart.com/vehicleFinder/
I hope that it makes sense, I have created a schema like this, and I am not just sure if it is usable and it is right. Here it is:
link to editor: https://dbdesigner.page.link/gS62T3GYPth2TAAbA
Thank you very much for any advice!
Best Answer
Why does 'type' have to be a separate table, and not just be an enum on 'model'? Then, when a user is doing a search of any type (via a dropdown or other method) it's just a filter on model (
... where type = ANY( VALUES ('BOAT'), ('MOTORBIKE')) ...).