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