Database structure for better searching or performance

Need help Database
1

Hello everyone.

I need help building (design in) a database for selling used vehicles.

Vehicles are in many categories like cars, motorcycles, caravans, and trucks.
All of these have subcategories. Let’s take the car as an example: Estate Car, Convertible (Cabriolet), SUV, Coupe, etc…
All vehicles have similar fields like Title, Description, Price, Photos, First registration, Mileage, and Power. Gearbox, Doors, etc…
Some of them have different fields. Like caravans have a number of beds. Or trucks have wheel formulas (4x2, 4x4, 6x2, etc)

Some of the fields are predetermined. Like categories, subcategories, first registration, mileage, etc… They have to select from the dropdown list to choose.

My first question is: Should I create a table for each vehicle category? Or it’s better if all fields are in the same table. And then I create a Satellite table for better search or performance. (Like when I have to create a post on a repeated group that contains a few fields: Title, Description, Price, etc.)

My second question is: The vehicles have many options and features that the user will have to choose from with checkboxes. How do I store them? Every option on its own field? (there are more than 60 options. it can go up to 100) Should I create a table only for those options? Or a list with one field?

My third question is: When you search for or add a vehicle let’s take an example a car. You have to choose the car’s make and model (those are predetermined). Should I create a table that contains a list of the Brand and one that contain the list of Models? Or should I create an Option Sets? (or all predetermined fields should be Option sets?)

My fourth question is: Each vehicle contains images. Up to 10 for each vehicle. Should I create a table only for images? Since there are many images for a single vehicle should I store them as a list? And how to choose which image to be the cover and the sorting of other images?

Thank in advance.
Fabian

2

I would create a option set of vehicle categories. I would store all vehicles in the same table and differentiate them with a field called “type” thats a pointer to the option set

these could be their own data type. you could have a list on each vehicle of it’s options

option set. definitely unless this data changes regularly and you need a better interface for updating this data. the OS is going to be your best bet.

i would store this as another table with a pointer to the original vehicle. You could query the image table by vehicle

1 Like
3

I feel like the option sets are going to be a bad idea. The interface is really bad for mass adding items (imagine adding 60-100 options manually in Bubble’s app editor), and if someone lists a vehicle and doesn’t see their option, then the app dev has to add that option in and push to live, then have everyone refresh their browsers…

I would just do datatype for everything.

Also @fabian.banushi BTW the NHTSA has a free API to just import every make, model, category, etc… It’s what all the major websites use, like when you shop for tires and you pick from dropdown of your Make, model

2 Likes
4

Jared.gibb thank you for your replies.

Could you please elaborate more this explanation?
What exactly should I do?

What about the featured image (cover) sorting other images as defined by the user?

Thank you again for your replies :smiley:
Fabian

5

tylerboodman thank you for your reply.

6

I would probably use a setup like this to keep things robust after hearing the last comment. If these are things that are likely to be updated on any regular basis.

-Make (data Type)

  • Name

-Model (data Type)

  • Name
  • Make Reference

-Options (data type)

  • Name
  • short Description
  • other relevant field

-Images (data type

  • Vehicle reference
  • Order (integer)
  • Cover (boolean)

-Vehicle

  • Make Reference
  • Model Reference
  • Year
  • Color
  • List Of Option References
1 Like
7

Thank you very much Jared.gibb. Very helpful.

Fabian

1 Like