Re: Uniqueness over two fields
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 03 Jan 2007 18:44:06 GMT
Message-ID: <WPSmh.40109$cz.592354_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.
Date: Wed, 03 Jan 2007 18:44:06 GMT
Message-ID: <WPSmh.40109$cz.592354_at_ursa-nb00s0.nbnet.nb.ca>
Brian Tkatch wrote:
> Bob Badour wrote: >
>>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.
> > Thanx! :) > > But, actually, the OP seems to have given quite a biit of information, > and i just gave a general idea, so your comment is unwwarranted.
Actually, the OP gave precious little information and absolutely none to support the very specific and explicit design recommendations you made.
Frankly, you imagined some set of requirements that may or may not exist and then proceeded to pontificate on the basis of your hallucinations.
Whether warranted or not, my comment was accurate and poignant. Received on Wed Jan 03 2007 - 19:44:06 CET