Is this a good solution to ensure data integrity in this specific situation?

I'm working on an applicatio开发者_如何学编程n 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?

---

You can use this dirty hack:

1. add a field is_official to price table, null as a value is possible in it
2. create an unique composite index priceId + is_official
3. for the official prices put 1 to is_official
4. for not official left it to be null

---

You could make the price table hold only official prices (with the figure possibly null), put a unique constraint on (businessId, itemId, locationId), and add another table of auxiliary prices referencing priceId.