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

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

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.

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.