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

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

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)
);