How to normalize a table that is indirectly dependent on a has-many relationship

normalization

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?

Best Answer

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
Related Question