Re: Uniqueness over two fields

Bob Badour wrote:
> 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.

Well, now we're both happy. :)

B. Received on Thu Jan 04 2007 - 16:24:02 CET