October 9, 2007 at 2:18 pm
I am currently reviewing a database design where the developer has several tables that include a column that stores a foreign key value to one of several tables, depending on the value of another column. Here's one of the tables:
CREATE TABLE [dbo].[SerialTracking]
(
[SerialTrackingID] [int] IDENTITY (1, 1) NOT NULL ,
[TransactionTypeID] [smallint] NOT NULL ,
[RecordID] [int] NOT NULL ,
[SerialNumber] [varchar] (20) NOT NULL ,
[DateEntered] [smalldatetime] NOT NULL ,
[EnteredBy] [varchar] (5) NOT NULL
) ON [PRIMARY]
The RecordID could hold the primary key value of more than one table. The TransactionTypeID value would indicate which one.
In my own experience, I have never designed a table this way and it will be make it impossible to have the database enforce referential integrity.
Has anyone else designed a table this way? I realize the alternative is to create separate tables for each "TransactionTypeID", but that's the way I think I would go. Are there other advantages or disadvantages to either approach that I may be missing?
Thank you for any comments or guidance you can provide.
October 9, 2007 at 4:27 pm
DO NOT DO THIS.
Using a column for more than one value is bad, bad design. It makes it hard to write queries, violates normal form, can't do RI, etc.
Better off with 50 columns that are TableAID, TableBID, TableCID, etc.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 12, 2007 at 12:07 am
Hi
Use different tables and then enforce referential integrity.
"Keep Trying"
November 28, 2007 at 1:13 am
A bit late, but i should mention one can find this design in many ERPs.
Just thing of 1000 or unkwnown and growing number of FKs.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply