Posted by Erland Sommarskog on 05/26/07 08:50

Khafancoder (khafancoder@gmail.com) writes:
> because copied products or parts info may needed to be changed by each
> owner.
> so, i will consider disabling identity insertion.

I still don't think this is a good design. Reasonably, there are
inherit properties with a product that does not depend on the store.

I think you need a new table ProductStores:

CREATE TABLE StoreProducts(StoreId bigint NOT NULL,
ProductId bigint NOT NULL,
CONSTRAINT pk_ProductStores PRIMARY KEY(StoreId,ProductId))

This table can then be augmented with columns that the store owner can
set as he pleases. If needed, you could also have a StoreProductParts,
with (StoreId, ProductId, PartNo) as key and with (StoreId, ProductId)
as foreign key to StoreProducts and (ProductId, PartNo) as foreign key
to Parts. But it seems funny to me that the same product would have
different parts in different stores.

But of course, I don't know what your real case is like. Maybe you are
only using stores, products and parts for the sake of the example?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]