Re: Uniqueness over two fields

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.

B. Received on Wed Jan 03 2007 - 18:36:14 CET