CODEWITHSUNDEEP
sqlpostgresqlreferenceforeign-key-relationship
phew
phew

Reputation: 826

PostgreSQL - can not figure out how to relate tables for certain task

I have a table beverage that looks like:

CREATE TABLE beverage (
    beverage_id SERIAL PRIMARY KEY,
    beverage    TEXT UNIQUE NOT NULL,
    description TEXT,
    sizeprice_id NOT NULL REFERENCES sizeprice ON UPDATE CASCADE ON DELETE CASCADE
);

I want to use a referencing column sizeprice_id within the beverage table to prevent adding beverages without specifying a 'prize per size'.

Furthermore these rules should apply:

  1. One beverage can have multiple sizes
  2. One beverage in a certain size can only have one price - if (Coke, 0.3, 2.50) exists one can not add a row (Coke, 0.3, 5.00)

Examples:

+----------+-------------+------+-------+
| beverage | description | size | price |
+----------+-------------+------+-------+
| Coke     | NULL        | 0.3  | 2.50  |
+----------+-------------+------+-------+
| Coke     | NULL        | 0.5  | 3.00  |
+----------+-------------+------+-------+
| Sprite   | small       | 0.2  | 2.00  |
+----------+-------------+------+-------+
| Sprite   | medium      | 0.3  | 2.75  |
+----------+-------------+------+-------+
| Sprite   | large       | 0.5  | 3.50  |
+----------+-------------+------+-------+

I'm not quite sure how to achieve that. I have been using a table with PRIMARY KEY (beverage_id, size, price) but that does not really work as a certain beverage could have different prices for one size. Also I had a reference from table beverages to table sizeprice and vice versa, reference loops should be avoided according to google.

I assume I do need more than one table to make it work as needed, probably have to split sizeprice into two tables size and prize. I was fiddling around with a FK in beverage that references a 1:M relation table size (one beverage can have several sizes), and that was referencing another table prize but I couldn't get that quite working either.

How do I achieve what I want?

EDIT: SQL-Fiddle

Upvotes: 0

Views: 45

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16487

You'd want a table beverage_types with columns type_id, name so you don't end up with products name "Coke", "coke" and "COKE". Also a table sizes with columns size_id, description, size. Finally that gives you a table beverage with columns id,type_id,size_id with FKs on type_id and size_id. Now you just enforce one price per size per type with a UNIQUE INDEX.

CREATE UNIQUE INDEX size_price_idx ON beverage (type_id,size_id);

Upvotes: 1

Related Questions