Re: Uniqueness over two fields
Date: Tue, 02 Jan 2007 18:14:58 GMT
Message-ID: <Cixmh.39671$cz.586081_at_ursa-nb00s0.nbnet.nb.ca>
Brian Tkatch wrote:
> 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.
> > A new TABLE for each store is silly; it should be one TABLE with stoire > as part of the PK. > > If you are tracking a store's items, and have many stores in the TABLE, > the natural PK of the TABLE is the store and the item id. The sku > should not be in the PK, so enforcing uniqueness is for data integrity > and throwing a unique INDEX (or CONSTRAINT) on store/sku makes a lot > of sense.
Wow, that's a lot of very detailed design advice you gave on the basis of no information whatsoever. Received on Tue Jan 02 2007 - 19:14:58 CET