How to make database structures for automotive products?

Question

MySQL

Given:

• - Brand
  
• - Model (when choosing a brand)
  
• - Generation (when choosing a model)
  
• - Equipment (when choosing a generation)
  
• - Body (when choosing a model)
  
• - Engine (when choosing a body)
  

(Each item has its own table, that is, there is a marka table - it contains all car brands, there is a model table - it contains all car models, etc.)

There is only one required value for a product - Model, the rest are optional

For a product you can specify several cars

There are two implementation options in the head:

1 . We create five columns (Model, Generation, Equipment, Body, Engine) for each column of the JSON type, we store the selected data (id) in it

An example of the resulting

prods table

id |           name          | model | pokolenie | komplektaciya | kuzov | dvigatel
     1 | Название товара |  {1,2}  |    {1,2,3}    |    {1,2,3,4,5}     |   {1}   | {1,2,3,4}

2 . We make connections, create 5 connecting tables.

For example, we create a table model and pokolenie

model

id | model_id | prod_id
     1 |       1       |    1
     2 |       2       |    1

generation

id | pokolenie_id | prod_id
     1 |           1         |    1
     2 |           2         |    1
     3 |           3         |    1

And a table with goods

prods

id |      name
     1 | Название товара

I like the first option more, because of its simplicity, fewer database queries and fewer records, estimated number of products> 5 ml, each product can have a dozen selected values ​​(model, generation, equipment, body, engine

) correctly to make structures?

Answer 1

Under no circumstances use JSON to store data that is supposed to be even ostensibly searchable. Before planning a database, it is recommended to read about database normalization

Why normalize a database?
You may have a question - why even normalize the database and deal with this redundancy?
The fact is that data redundancy creates the prerequisites for the appearance of various anomalies, reduces productivity, and makes data management inflexible and not very convenient. From this we can conclude that normalization is needed to:
Eliminate anomalies
Improve performance
Improve the convenience of data management

Answer 2

Now show how your first option would look like if, for example, model 1 in trim 1 can have engines 2 and 3, in trim 3 engines 3 and 4, and in other trim levels it is not available. And the Model 2 comes in trims 2, 3, 4 and 5, but the engine also depends on the trim. And if it still depends on the generation?

Answer 3

Well, something like that, I guess.

Answer 4

4 tables
Manufacture manufacturer
Model/stage
Options body parts and miscellaneous
Parts List of all parts from options
Plus a few auxiliary tables from the original manufacturer purely for convenience. Lowered the accounting and your machinery