Re: Uniqueness over two fields

DONNA wrote:
> I'm trying to make a database that indexes items sold at online stores.
>
> One table called 'Stock' will contain items sold at every store included
> in the project. It will have the fields ID, SKU, Store.
>
> ID will be a unique primary key that respresents the item
> SKU will be the ID that is used to identify it at that store
> Store will respresent the store that is selling it
>
> Now each store can only sell one of each item so I'd like SKU to be a
> unique field. Problems is, two stores might have the same SKU so
> uniqueness would cause it to break.
>
> What can I do to get around this problem? I could combine SKU and Store
> into one field unique. Or I could make a new table for each Store, but I'd
> rather have everything in one long table as the front-end will be
> displaying all the data together, not per store.

Enforcing physical uniqueness over (store + sku) by indexation prevents a normalized consolidated schema because it breaks the Closed World Assumption. Such assumption means that any non existing tuples in a relvar necessarily represent a proposition validated by FALSE. In the case of (store + SKU), there is no way that all non existing propositions such as Item #(store + SKU) can be validated to FALSE because of the concatenation.

Based on the element you have proposed, the only reliable candidate key would be (Store + ID) with ID being the unique identifier of item relvar that is independent from store. In fact, SKU has no use at all and should be taken out if you want to consolidate your schema.

You may however use Bob Badour's advice into applying uniqueness by creating a constraint for implementing uniqueness over (Store + ID)

Hope this helps...
Merry Christmas all and happy new year!!! Received on Wed Jan 03 2007 - 11:20:12 CET