How a table FK should reference one of several tables

Last Post 16 Aug 2006 03:28 AM by mwesch. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
oferfr2
New Member
New Member

--
16 Aug 2006 02:04 AM
Hi,

What's the best way to implement a table with a foreign-key that points to a record of one of several tables?
(See example below).

How to write the check constraint expression?
See my separate post

Requirements – maximum data-integrity checks at the database level.
What's the performance penalty?
For each suggestion, please list the pros and cons.


Thanks!

Example:

A shop that sells pens or telephones.

The ID is a Pen ID or a TelephoneID, depending on the ShopItemType;



SHOPITEM
ShopItemID
ShopItemType (PEN OR TELEPHONE)
Title

Description
ID
Price
PEN
PenID
Color

TELEPHONE
TelephoneID
Model
IsCellular
mwesch
New Member
New Member

--
16 Aug 2006 03:28 AM
You need to change your perspective.

SHOPITEM
--------------
ShopItemID [PK]
ShopItemType
(attributes common to all items)
Price
Description
Model


PEN
--------------
PenID [PK]
ShopItemID [FK]
(attributes specific to pens)
Color

TELEPHONE
--------------
TelephoneID [PK]
ShopItemID [FK]
(attributes specific to telephones)
IsCellular
SQLUSA
New Member
New Member

--
18 Aug 2006 02:31 AM
ShopItemType shoule be a foreign key ShopItemTypeID pointing to the PK in ShopItemType table.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
You are not authorized to post a reply.

Acceptable Use Policy