Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.ision.net!newsfeed2.easynews.net!ision!citadel.nobulus.com!News.Dal.Ca!ursa-nb00s0.nbnet.nb.ca!53ab2750!not-for-mail From: Bob Badour User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.theory Subject: Re: Uniqueness over two fields References: <1167757454.251131.53970@42g2000cwt.googlegroups.com> In-Reply-To: <1167757454.251131.53970@42g2000cwt.googlegroups.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 33 Message-ID: Date: Tue, 02 Jan 2007 18:14:58 GMT NNTP-Posting-Host: 142.176.57.198 X-Complaints-To: abuse@aliant.net X-Trace: ursa-nb00s0.nbnet.nb.ca 1167761698 142.176.57.198 (Tue, 02 Jan 2007 14:14:58 AST) NNTP-Posting-Date: Tue, 02 Jan 2007 14:14:58 AST Organization: Business Internet Xref: news.f.de.plusline.net comp.databases.theory:40692 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.