lundi 28 octobre 2019

Two tables to one table as relation

I have some troubles to design a part of my database and the relationship between them.

My entities are:

  • Vehicles -> 1:1 -> Prices
  • Bikes -> 1:1 -> Prices

Option A:

Create 2 tables

VEHICLES TABLE

+----------------+---------+
|      NAME      |  TYPE   |
+----------------+---------+
| Id             | uuid    |
| brand          | varchar |
| model          | varchar |
| attribute1     | varchar |
| price          | float   |
| discount float |         |
| locale varchar |         |
+----------------+---------+

BIKES TABLE

+----------------+---------+
|      NAME      |  TYPE   |
+----------------+---------+
| Id             | uuid    |
| vendor         | varchar |
| attribute1     | varchar |
| attribute2     | varchar |
| attribute3     | varchar |
| price          | float   |
| discount float |         |
| locale varchar |         |
+----------------+---------+

Option B

Create 3 tables

VEHICLES TABLE

+----------------+---------+
|      NAME      |  TYPE   |
+----------------+---------+
| Id             | uuid    |
| brand          | varchar |
| model          | varchar |
| attribute1     | varchar |
+----------------+---------+

BIKES TABLE


+----------------+---------+
|      NAME      |  TYPE   |
+----------------+---------+
| Id             | uuid    |
| vendor         | varchar |
| attribute1     | varchar |
| attribute2     | varchar |
| attribute3     | varchar |
+----------------+---------+

PRICES TABLE

+----------------+-------+
|      NAME      | TYPE  |
+----------------+-------+
| vehicle_id     | FK    |
| bike_id        | FK    |
| price          | float |
| discount float |       |
| locale varchar |       |
+----------------+-------+

Option C

create 4 tables

VEHICLES TABLE

+----------------+---------+
|      NAME      |  TYPE   |
+----------------+---------+
| Id             | uuid    |
| brand          | varchar |
| model          | varchar |
| attribute1     | varchar |
+----------------+---------+

VEHICLES_PRICES TABLE

+----------------+-------+
|      NAME      | TYPE  |
+----------------+-------+
| vehicle_id     | FK    |
| price          | float |
| discount float |       |
| locale varchar |       |
+----------------+-------+

BIKES TABLE


+----------------+---------+
|      NAME      |  TYPE   |
+----------------+---------+
| Id             | uuid    |
| vendor         | varchar |
| attribute1     | varchar |
| attribute2     | varchar |
| attribute3     | varchar |
+----------------+---------+

BIKE_PRICES TABLE

+----------------+-------+
|      NAME      | TYPE  |
+----------------+-------+
| bike_id        | FK    |
| price          | float |
| discount float |       |
| locale varchar |       |
+----------------+-------+

NOTE: I really simplify the vehicles & bikes tables (i can't merge this both in one table like 'product')

What we will be the best design for the performance and pattern point of view?

Aucun commentaire:

Enregistrer un commentaire