I am designing a database for a system for registering orders for a pizzeria, but a couple of problems arose:

I have a table called products that contains these attributes:

| PRODUCTS              |
| --------------------- |
| id          | int     |
| name        | varchar |
| description | varchar |
| url_image   | varchar |
| price       | decimal |
| size        | varchar |
| published   | boolean |

I had a problem with the price and the sizes so I normalized in 3 tables: Prices, Sizes and Products that should be related:

| PRODUCTS              |
| --------------------- |
| id          | int     |
| name        | varchar |
| description | varchar |
| url_image   | varchar |
| published   | boolean |

| SIZES          |
| -------------- |
| id   | int     |
| size | varchar |

| PRICES          |
| --------------- |
| id    | int     |
| value | decimal |

1 pizza can have different sizes such as personal, family, etc, each size of pizza has a certain price which is also affected by the taste of the pizza, for example 1 family size ham pizza can cost 10 dollars while 1 pizza Hawaiian of the same size can cost 15 dollars, you also have to take into account that a product can not only be a pizza but a soda or another that the business requires.

How can I relate these 3 tables? Or is it unnecessary to take price and size as entities?

CodePudding user response：

Consider:

• products - a table of generic products
• product_variants - a table of variations based on size, etc, and price.

Do not "normalize" (id value) numeric things, such as price -- it makes it messy and slow to search on the number, especially numeric ranges.

CodePudding user response：

IMO, Prices should not be an individual entity, instead it should just be an attribute inside Products unless a lot of products have same prices. However for Sizes, you can add additional details such as the length, width as optional attributes for pizzas and leave it empty of other products etc, and then reuse it since all products should have a specific size. So each product should have a foreign key named something like size_id, since you mentioned it should not only be pizzas, the data of Sizes table should contains data like "L", "M", "S", "100ml" etc.

Or else you can just simply use the original table which all of these 3 are combined.