enumerations DRI design
-
Has anyone tripped across this one? I have an enumerations table (LOOKUP table) housing many different 'types' of lookup values (eg. States, Countries, etc..). I'd like to enforce declaritive RI but the PK on my enumerations table does not 'logically' map to specific row values.
Any suggestions? Would a constraint or trigger do the trick? If so, does anyone have sample DDL/SQL to accomplish?
CREATE TABLE [dbo].[Enumerations] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[EnumType] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EnumLanguage] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EnumIntValue] [int] NOT NULL ,
[EnumStringValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayText] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DisplayOrder] [int] NOT NULL ,
[SystemCreateDate] [datetime] NOT NULL ,
[SystemCreateUser] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SystemUpdateDate] [datetime] NOT NULL ,
[SystemUpdateUser] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Enumerations] WITH NOCHECK ADD
CONSTRAINT [PK_Enumerations] PRIMARY KEY CLUSTERED
( [ID] 
WITH FILLFACTOR = 50 ON [PRIMARY]GO
BT -
Get rid of Enumerations and have separate tables for States, Countries etc.
-
Wish it were that simple. I inherited this design and it's fixed across a large, 1 TB SQL DB environment. To alter the design would require altering dozens (potentially hundreds) of methods, procs, etc...
FYI, the enumerations entity houses upwards of 50+ lookup entity variations.
BT -
Skip:
I, too, have suffered from the sins of the fathers (and mothers) with a similar setup. Assuming that you store the value of the ID column in each child table and EnumType = something like 'State', 'Country', then triggers are the way to go. But you also have to put triggers on all the tables where you're storing ID as a fake FK to Enumerations to ensure that you have RI. But if it were up to me, I wouldn't bother with triggers now. Since your app(s) are probably already coded to only load a listbox with, for example, state names, I don't see what you gain by coding the triggers as well. Especially since if you have fifty+ EnumTypes, you will have that many triggers, plus duplicate trigger code where you reference an EnumType on different tables. Unless you have a lot of apps that return just plain wrong data from dbo.Enumerations; i.e., it hands you a Country where you were expecting a State ("123 Wall St, New York, Bangladesh(?) 20012")
Luck, Dave
There is no "i" in team, but idiot has two.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply