Re: Uniqueness over two fields

> One table called 'Stock' will contain items sold at every store included
> in the project. It will have the fields ID, SKU, Store.
> ...two stores might have the same SKU

Assuming each store can only have one sku for each item, and different stores could have the same sku for the same item, an alternate schema might be as follows:

T_Store (id, name, etc)
T_Item (id, name, etc)
T_SKU (id, name)
T_StoreStockSku (storeID, itemID, skuID)

Below is an alternate solution using dbd that allows each store to have 0 to many sku's for each stocked product. In the example, stores Walmart, Sears and Kmart stock the same cd. Walmart and Sears each have a single and different sku, while Kmart has two and they are the same as Walmart's and Sears'.

(new 'walmart 'store)
(new 'sears 'store)
(new 'kmart 'store)

(new 'stock 'verb)
(new 'cd1 'product)
(new 'as 'preposition)

(new 'wm111 'sku)
(new 'sr111 'sku)

(set walmart stock cd1 as wm111)
(set sears stock cd1 as sr111)
(set kmart stock cd1 as wm111)
(set kmart stock cd1 as sr111)

(; Get stores that stocks cd1)
(; Gets walmart, sears and kmart)
(get * stock cd1)

(; Get stores that stocks cd1 as sr111)
(; Gets sears and kmart)
(get * stock cd1 as sr111)

(; Get stores that stock a product as sku wm111)
(; Gets walmart and kmart)
(get * stock (get product instance *) as wm111)

(; Get the product that kmart stocks as wm111 and sr111)
(; Gets cd1)
(and (get kmart stock * as wm111)

       (get kmart stock * as sr111)) Received on Wed Jan 03 2007 - 22:07:47 CET