I am designing a database for an order registration system for a pizzeria. I have a table called "products" that contains these attributes:
| PRODUCTOS |
| --------------------- |
| id | int |
| nombre | varchar |
| descripción | varchar |
| url_imagen | varchar |
| precio | decimal |
| tamaño | varchar |
| publicado | boolean |
When entering records in said table, a redundancy in the data was generated, so I decided to separate them into 3 tables: Prices, Sizes and Products that are related as follows:
| PRODUCTOS |
| --------------------- |
| id | int |
| nombre | varchar |
| descripción | varchar |
| url_imagen | varchar |
| publicado | boolean |
| TAMAÑOS |
| ---------------- |
| id | int |
| tamaño | varchar |
| PRECIOS |
| --------------- |
| id | int |
| valor | decimal |
1 product can have different sizes and 1 same size can be present in one or several products, in the same way 1 product can have different prices in relation to its size and flavor and 1 same price is usually repeated for many products, also not all products they have one size and only have one single price.
Example:
A product in this case 1 ham pizza has these sizes: personal size, medium size and family size, its price varies according to its size and according to its flavor in this way:
- 1 personal ham pizza costs 7 euros.
- 1 medium ham pizza costs 10 euros.
- 1 family ham pizza costs 15 euros.
While a Hawaiian pizza only has the medium and large size available and its price changes only on the large pizza:
- 1 medium Hawaiian pizza costs 10 euros.
- 1 large Hawaiian pizza costs 20 euros.
Here other examples of different products:
- Coca-Cola 1.5L costs 2 euros.
- Coca-Cola 2L costs 2.5 euros.
- Pepsi 2L costs 2 euros.
- Lasagna costs 5 euros (It has no size and only has a single price).
The first thing I did was relate my table of products and sizes with a many-to-many relationship, so far everything is correct.
| PRODUCTO_TAMAÑO |
| ------------------ |
| id | int |
| producto_id | int |
| tamaño_id | int |
But for the price table I can't find a way to relate it, what I tried was to add a price relationship with the pivot table of products and sizes. But as in many products the price is usually the same, this ends up generating a redundancy in prices.
| PRODUCTO_TAMAÑO |
| --------------------- |
| id | int |
| producto_id | int |
| tamaño_id | int |
| precio | decimal |
How can I relate these 3 tables in the most optimal way?