CODEWITHSUNDEEP
database-designdatabase-normalization
user2126081
user2126081

Reputation: 285

Database Design with products, attributes and prices

What is the best approach to achieve product with multiple of prices and the prices depend on its attribute at the same time? I want to store orange juice (small) $10 and orange juice(large) $14. How should I manage these records? Here is my work around.

**products**
----------
id    name
1     orange juice

**prices**
----------
id    product_id    price
1     1             10
2     1             14

**sizes**
----------
id    product_id    size
1     1             small
2     1             large

**prices_sizes**
----------
id    price_id    size_id
1     1           1
2     2           2

Or should I have something like products_prices_sizes?

Upvotes: 0

Views: 1120

Answers (2)

philipxy
philipxy

Reputation: 15118

Yes, you should "have something like products_prices_sizes". There is no reason not to. The design you give is encoding (product price, size) triplets as pairs of redundant pairs, ((product price), (product size)). It's also not clear why you used ids for pairs.

The straightforward simplest design is:

-- "product PRODUCT in size SIZE has price PRICE"
Product(product, size, price)

To see whether you should decompose this into smaller tables (that join back to it) you need to learn about normalization. It happens that in this case, from our common sense understanding of your application, {name, size} is the only candidate key and the table is in 5NF and shouldn't be decomposed into simpler tables. Just because there is one price per name-size pair, that doesn't mean you have to have a binary relationship. And I have no idea why you proposed a pair of pairs.

You probably want product ids. You might want size ids. It's extremely unlikely that you would want price ids. But you should have a reason for having ids instead of or in addition to "natural keys". (And there are reasons.) (And you should learn them.) But that has nothing to do with whether there should be a 3-way relationship.

Upvotes: 1

user2314737
user2314737

Reputation: 29307

Something like this: each product and each size has an id, the prices table assigns a price to a pair (product_id, size_id)

**products**
----------
id    name
1     orange juice

**sizes**
----------
id      size
1       small
2       large

**prices**
----------
id    product_id    size_id     price
1     1             1           10
2     1             2           14

Upvotes: 0

Related Questions