CODEWITHSUNDEEP
sqlsql-server
DRobertE
DRobertE

Reputation: 3508

SQL Server Table Design with Table having foreign key to two other tables

I have Four tables,
DiscountCode which describes a discount. Bundles which is basically sold as a product so it contains a productcode Products which are sold as products so it also contains a productcode ProductDiscount code meant to be used to describe the fact that a Bundle/Product can have multiple discount codes associated with it.

**Discount Code**
Id
Name
Code  ex. SUMMER10  ie 10% off summer products
...

**Bundles**
Id
Name
ProductCode  *Unique*  Ex..ABC123
...

**Products**
Id
Name
ProductCode  *Unique*  Ex.. XYZ1234
...

**ProductDiscountCode**
DiscountId
ProductCode   FK to both Products.ProductCode  AND Bundles.ProductCode

Records in ProductDiscountCode:
1   ABC123
1   XYZ1234
1   URS576  <-- prevent this if Prod or Bundle does NOT contain URS576

My question is in regards to the ProductCode in the Ternary table. NOW KEEP IN MIND PELASE SQL IS NOT MY STRONG SUIT! Can/Should the ProductCode column in the ternary table be foreign keyed to TWO Separate tables in an attempt to restrict it's content to a product code that is EITHER contained in the Bundle table OR the Product table, assuming the productcode in bundles and products is unique between the two tables (to be enforced by business rules). Or does the ProductDiscountCode table need the following

**ProductDiscountCode**
DiscountId
ProductCode  FK to ProductCode in Product
BundleCode   FK to ProductCode in Bundle

Upvotes: 0

Views: 145

Answers (3)

Tom Page
Tom Page

Reputation: 1241

If you're going to use a Foreign Key then every value must exist as a key on the other table so you can't constrain your column Product Keys if it's got Bundle Keys in it and vice versa.

If you need to ensure referential integrity then you'll need to have two Nullable columns.

You should probably also have a Check Constraint to make sure that one and only one of the codes is null.

CREATE TABLE ProductDiscountCode
(
DiscountId int,
ProductCode  varchar(12) null,
BundleCode   varchar(12) null,

CONSTRAINT ProductDiscountCode_PK PRIMARY KEY(DiscountId),

CONSTRAINT FK_ProductDiscountCode_DiscountId FOREIGN KEY (DiscountId) REFERENCES DiscountCode (Id),
CONSTRAINT FK_ProductDiscountCode_BundleCode FOREIGN KEY (BundleCode) REFERENCES Bundle (ProductCode),
CONSTRAINT FK_ProductDiscountCode_ProductCode FOREIGN KEY (ProductCode) REFERENCES Product (ProductCode),

CONSTRAINT ProductCodeExists CHECK (
                                   (ProductCode IS NULL AND BundleCode IS NOT  NULL) 
                                   OR 
                                   (ProductCode IS NOT NULL AND BundleCode IS NULL)
                                   )
)

If for some reason you really need to be able to show the Product Codes as a single column then you could do something along these lines

CREATE TABLE ProductDiscountCode
(
DiscountId int,
SingleProductCode  varchar(12) null,
BundleCode   varchar(12) null,
ProductCode as ISNULL (SingleProductCode  ,BundleCode  ) ,
CONSTRAINT ProductDiscountCode_PK PRIMARY KEY(DiscountId),
CONSTRAINT FK_ProductDiscountCode_DiscountId FOREIGN KEY (DiscountId) REFERENCES DiscountCode (Id),
CONSTRAINT FK_ProductDiscountCode_BundleCode FOREIGN KEY (BundleCode) REFERENCES Bundle (ProductCode),
CONSTRAINT FK_ProductDiscountCode_SingleProductCode FOREIGN KEY (SingleProductCode) REFERENCES Product (ProductCode),
CONSTRAINT SingleProductCodeExists CHECK ((SingleProductCode IS NULL AND BundleCode IS NOT  NULL) OR (SingleProductCode IS NOT NULL AND BundleCode IS NULL))
)

But you do have to ask yourself first whether using Foreign key restraints is actually necessary in the first place.

Having two columns for product code could speed up your Select queries slightly but by having to decide which column you are storing the product code in the updates, deletes and inserts will be made more complicated.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96650

OK, it is never a good idea to try to constrain to two differnt FKs for the same field, that is a sign of incorrect design.

Why is bundle a separate table if it is not using the product codes from the product table? WHy not add a column to the product table to identify if the line item is a bundle or an individual product and stopre both there?

Upvotes: 1

Yanire Romero
Yanire Romero

Reputation: 482

Please first take a look at the thread: Multiple foreign key constraints on a single column in SQL Server 2008

ProductCode is a FK in Bundle and then you want another foreign key to the same column in ProductDiscountCode. You are double referencing same field and that does not seem to be good designing. You should restrict the values in the table at the moment of the insert, constraints are not meant for all kinds of validations.

Upvotes: 0

Related Questions