I'm working on an application which tracks prices for certain items.

Each price has a reference to an item, a business that sells that item, and the location the item is being sold at. Now, normally, this would do just fine:

CREATE TABLE `price` (
  `priceId` INT UNSIGNED NOT NULL AUTO_INCREMENT, -- PK
  `businessId` INT UNSIGNED NOT NULL,
  `itemId` INT UNSIGNED NOT NULL,
  `locationId` INT UNSIGNED NOT NULL,
  `figure` DECIMAL(19,2) UNSIGNED NOT NULL,
  -- ...
)

But I have the following problem:

The application logic is such that one item at one business at one location can have multiple prices (at this point it's not really important why), and one of those prices can be an official price - an item doesn't have to have an official price, but if it does, there can be only one.

The question is; how to model this to ensure data integrity?

My initial idea was to create an additional table:

CREATE TABLE `official_price` (
  `priceId` INT UNSIGNED NOT NULL -- PK + FK (references price.priceId),
  -- ...
)

This table would hold priceId:s for prices that are official, and the PK/UNIQUE constraint would take care of the 'one-or-none' constraint.

This seems like a workable solution, but I'm still wondering if there's a better way to handle this situation?

Originally asked by: Martin on Stack Overflow