Question 113210 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Products with different prices and sizes

I find myself designing a database for a system for registering orders for a pizzeria.
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 |

When entering records in said table, a redundancy in the data was generated, so I decided to separe them into 3 tables: Prices, Sizes and Products that must be related as follows:

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

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

| PRICES          |
| --------------- |
| id    | int     |
| value | 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, it should be noted that not all products have a size and only have a single price.

Example:

A product in this case 1 ham pizza has these sizes: personal size, medium size and large size, its price varies according to its size and according to its flavor in this way:

  • 1 small ham pizza costs 10 dollars.
  • 1 medium-sized ham pizza costs 15 dollars.
  • 1 large ham pizza costs 20 dollars.

While a Hawaiian pizza only has the medium and large size available and its price changes only on the large size pizza:

  • 1 medium size Hawaiian pizza costs 10 dollars.
  • 1 large Hawaiian pizza costs 25 dollars.

Here other examples of different products:

  • Coke 1.5L costs 2 dollars.
  • Coke 2L costs 2.5 dollars.
  • Pepsi 2L costs 2 dollars.
  • Lasagna costs 5 dollars (It has no size and only has a single price)

The first thing I did was relate my products and sizes table with a many-to-many relationship, so far everything was correct.

| PRODUCT_SIZE     |
| ---------------- |
| id         | int |
| product_id | int |
| size_id    | int |

But for the price table I not 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.

| PRODUCT_SIZE         |
| -------------------- |
| id         | int     |
| product_id | int     |
| size_id    | int     |
| price      | decimal |

How can I relate these 3 tables in the most optimal way?

Tags:

2

Answers


  1. 0

    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.

    Hope this helps.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search