SQL Table with two relations or table with one relation and repeated rows

Question

This is an example, the names are fictitious.

On the one hand, we have suppliers who provide products to shops:

Suppliers

id name

1  GreatSupplier
2  SuperSupplier

On the other hand, we have shops that sell products to consumers:

Shops

id name          supplier

1  NiceShop      null
2  ShopShop      null
3  Soop          1
4  CheapShop     1
5  MyShop        1
6  Shopping      2 

There are shops that have their own prices like NiceShop or ShopShop, so they don't have suppliers. But there are shops that use the prices set by the supplier like Soop, CheapShop, MyShop or Shopping.

Then I want to show all the prices of the products that the shops show to their customers. Something like this:

NiceShop  - Tomate: 1.23      // shop price
ShopShop  - Tomate: 1.26      // shop price
Soop      - Tomate: 1.21      // supplier 1 price
CheapShop - Tomate: 1.21      // supplier 1 price
MyShop    - Tomate: 1.21      // supplier 1 price
Shopping  - Tomate: 1.19      // supplier 2 price

Two options come to mind:

Option 1:

Products

id id_product   id_shop   id_supplier   price
1  34           1         null         1.23
2  34           2         null         1.26
3  34           null      1            1.21
4  34           null      2            1.19

When displaying prices, if it is a row with id_shop I show it as is, but if it is a row with id_supplier I join the supplier and the shops.

Here I can't make a unique index between id_product-id_store-id_supplier and things like this could happen:

id id_product   id_shop   id_supplier   price
5  34           3         null          1.21 // wrong

This should not happen as shop 3 has supplier 1 and this is already inserted in id 3.

Option 2:

Another option would be:

• If we insert a price from a shop that does not have a supplier, it is inserted as is.
• If we insert a price from a supplier, a join is made between the supplier and the shops and the same price is inserted several times. (same for updates)

Products

id id_product   id_shop   price

1  34           1         1.23      // shop price
2  34           2         1.26      // shop price
3  34           3         1.21      // supplier 1 price
4  34           4         1.21      // supplier 1 price
5  34           5         1.21      // supplier 1 price
5  34           6         1.19      // supplier 2 price

This option is a bit cleaner and allows me to create a single id_product-id_shop index but I am creating a lot of records with repeated prices, in this example it is duplicated 3 times but in my real environment it can be duplicated 50 times, and that translates into several extra gigabytes of database space.

Is there a better way to do this?

Answer 1

Most of the issues described can be solved by creating a common associated table for both shops and suppliers that is used to associate prices. In my example, I'll call this the price_setters table.

A Shop's Price-Setter is either itself or its supplier. This is enforced by the constraint on price_setters that the record has either a unique shop_id or a unique supplier_id, but not both.

prices has a FK to price_setters rather than shops or suppliers.

The only "anomaly" remaining is that there is no requirement in the schema for a shop or supplier to have a price_setter; however, that would just prevent you from assigning prices to the entity.

Edit: I moved the FKs between price_setters, shops, and suppliers into price_setters

-- Retrieve all prices by shop by going through the price_setter

select 
  shops.name,
  products.name,
  prices.price
from shops
left join suppliers 
  on shops.supplier_id = suppliers.id
join price_setters 
  on shops.id = price_setters.shop_id 
  or suppliers.id = price_setters.supplier_id
join prices 
  on prices.price_setter_id = price_setters.id
join products 
  on prices.product_id = products.id
;

create table suppliers (
  id unsigned bigint primary key, 
  name varchar(255) not null
);

create table shops (
  id unsigned bigint primary key,
  name varchar(255) not null,
  supplier_id unsigned bigint,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

create table products (
  id unsigned bigint primary key,
  name varchar(255) not null
);

-- A shop's price setter is either itself or its supplier
create table price_setters (
  id unsigned bigint primary key,
  shop_id unsigned bigint ,
  supplier_id unsigned bigint,
  FOREIGN KEY (shop_id) REFERENCES shops(id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  unique(shop_id),
  unique(supplier_id),
  CONSTRAINT chk_shop_xor_supplier CHECK (
    (shop_id IS NOT NULL AND supplier_id IS NULL)
        OR (shop_id IS NULL AND supplier_id IS NOT NULL)
  )
);

create table prices (
  id unsigned bigint primary key,
  product_id unsigned bigint not null,
  price_setter_id unsigned bigint not null,
  price float not null,
  unique(product_id, price_setter_id),
  FOREIGN KEY (price_setter_id) REFERENCES price_setters(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Answer 2

If you want to have it clean you will need 3 entities (apart from shops and suppliers):

Products (PK = id_product)

id_product   name (and other attributes)
34           Tomate

Shop prices (PK = id_product + id_shop)

id_product   id_shop   price
34           1         1.23
34           2         1.26

Supplier prices (PK = id_product + id_supplier)

id_product   id_supplier   price
34           1             1.21
34           2             1.19

In both shop prices and supplier prices product/shop/supplier ids are not null and foreign keys to corresponding tables.

To display a list you will need to union both price tables:

SELECT s.id as id_shop, shp.id_product, shp.price
  FROM shops s
  JOIN shop_prices shp ON (shp.id_shop = s.id AND s.supplier_id IS NULL)
 UNION ALL
SELECT s.id as id_shop, sup.id_product, sup.price
  FROM shops s
  JOIN supplier_prices sup ON (sup.id_supplier = s.id_supplier)

(+extra joins with products if you need to display product attributes like name...)

This structure can't prevent eg. adding a record to shop prices for a shop that has a supplier, as in your example:

id_product   id_shop   price
34           3         1.21

But if you're careful such record wouldn't even show, for example in query above it is filtered out with s.supplier_id IS NULL in join condition. If it's mandatory, such case can be caught in a trigger or just at application level.

Answer 3

Can you just add shops without suppliers to the suppliers table? Also - this phrase scares me:

bit complicated to maintain in the updates

I would use views to display the prices - not new tables.

I would also consider adding some form of versioning to your product/price table. Two ways to do this: add begin_ts and end_ts where end_ts = '9999-12-31' means the record is still active. Or alternatively, have a trigger that automatically tracks when a record changes and writes the record to an archive table. Bottom line is you will probably want something that tracks the price history so you can show what the prices where X days, weeks, months, years ago.