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?
Technically you could do it the same as with the other table. You could create a pivot table
PRODUCTO_PRECIO
with the columns (id, product_id, price_id)I would n't do it that way anyway . Especially for the price table. It makes no sense to have the prices in another table so that they can be reused by other products because the price is a very variable data. Perhaps not so much in a pizzeria, but it is uncommon to repeat prices.
Also, imagine that two products share a price and you want to change the price of only one of them.
With this structure, what you would have to do is eliminate the PRODUCT_PRICE record, see if you already have the price defined in the price table, if you do not have it defined, define it, and finally create a record in the product_price table that relates your product and the New price.
Or simply think that although the prices are now: 2.5, 2 and 5 (which are often repeated), your client's requirements may change and start to have more 'random' and less repeated prices.
There is no point in trying to normalize the price column. Just leave it in the products table as you initially had.
The case of sizes is a little different. You can normalize it in another table if you want, but for this particular case you have something better: enumerated types (in other databases you could do the same with check constraints)
With enumerated types you guarantee that you cannot insert/update that column with a value that is not one of the allowed values in the list.
It would only make sense to output the size in another table if you wanted to save size properties. For example:
(id_size, name, description, minimum_height, minimum_width, minimum_depth, ...)
If you need something like that, you'll have to get it out on a board. If all you need is to ensure that the size column always accepts a series of valid values, the enumerated type is your best option.