How do you normalize a table that is indirectly dependent on a has-many relationship? I currently have the following database design:


Product: contains various description attributes


Belongs to many ProductSets

ProductSet:


Has many Products
Belongs to many Stores

Store:


Has one ProductSet
Must, through some set of relationships, specify the price for each product in its product set



I'm specifically struggling with the best way to incorporate the final bullet in a normalized way. So far, the only solution I've come up with is to have a join table between Store and Product, but this doesn't guarantee that the store has prices for all of (and only) the products in its ProductSet. What (if any) is the best way to accomplish this?
You can create a table ProductPrice:

CREATE TABLE ProductPrice (
  ProductID int NOT NULL,
  ProductSetID int NOT NULL,
  StoreID int NOT NULL,
  Price numeric(10,2) NOT NULL,
  PRIMARY KEY (ProductID, ProductSetID, StoreID),
  FOREIGN KEY (ProductID, ProductSetID) REFERENCES ProductProductSet,
  FOREIGN KEY (StoreID, ProductSetID) REFERENCES Store
)


ProductProductSet beging the many-to-many relationship between Product and ProductSet.

These two foreign keys would ensure that the price refers to a store that is intended to have such product. Then you can create a query to find out which prices are missing:

SELECT s.*, p.*
FROM Product p
JOIN ProductProductSet pps ON (pps.ProductID = p.ProductID)
JOIN Store s ON (s.ProductSetID = pps.ProductSetID)
LEFT JOIN ProductPrice pp ON (pp.ProductID = p.ProductID AND pp.StoreID = s.StoreID)
WHERE pp.ProductID IS NULL