How to apply Foreign Keys on top of a Common Lookup table

I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views. Although doable is there another solution I am not seeing? I have scoured the web in search of an answer knowing I cannot be the only SQL developer in this situation. I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.

CREATE TABLE dbo.CodesTable (
PartyGrpId INT  NOT NULL
  , CdTyp  VARCHAR ( 8 ) NOT NULL
  , CompId INT  NOT NULL
  , CdValue VARCHAR ( 8 ) NOT NULL
  , CdValueDesc VARCHAR ( 255 ) NULL
  , AltValueDesc VARCHAR ( 100 ) NULL
  , DefaultInd CHAR ( 1 ) NULL
  , OrderNum SMALLINT NULL
  , ActiveCd CHAR ( 1 ) NULL
  , ExpireDtm SMALLDATETIME NULL
  , EffectDtm SMALLDATETIME NULL
  , ModById INT  NULL
  , ModDtm SMALLDATETIME NULL
  , CreateById INT  NULL
  , CreateDtm SMALLDATETIME NULL
  , CONSTRAINT PC_dbo_EcdDetail
    PRIMARY KEY CLUSTERED ( PartyGrpId ASC, CdTyp ASC, CompId ASC, CdValue ASC )
    ON FG_Data
) ON FG_Data;

I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this. :)

ALTER TABLE dbo.BusinessStatus WITH NOCHECK
ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'S

April 29th, 2015 9:07am

> looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views

That does not strike me as viable solution.

How about a stored procedure to enforce RI?

Free Windows Admin Tool Kit Click here and download it now
April 29th, 2015 9:16am

>> I have several common lookup tables where many of the "codes" reside for many different code types. <<

No! This is called Automobiles, S quids and Lady Gaga SQL and laugh at you or fire you or both. A table is a set; a set has one and only one kind of element it it. This is the basis of RDBMS and First Normal Form. 

This is so bad it has a name; OTLT for One True Lookup Table ;I give an example of how stupid this in one of my books where a Dewey Decimal Classification for Churches is the same as the ICD code for deformed testicles. 

There is no such crap as a generic_type_code in RDBMS. It either a <something in particular>_type or a <something in particular>_code in data modeling and the ISO-11179 standards.

You have more NULL-able columns in one table than you should have in an entire schema! 

You have audit data (creation and modification) in the row under audit. This is both stupid and illegal. You cannot expose the audit trail to the data user by law. When you delete a row, you also destroy the audit trail Doh! 

You have no CHECK() constraint on the (effective_date, expiry_date) pair. 

Putting _table in a table name is a design error called a tibble to make fun of how silly it. You might want to download the PDF of bad SQL code smells from Red Gate so you can avoid things like this. 

>>  the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same. <<

_value and _code are both what ISO-1179 calls an attribute property. It is a silly as a list of adjectives without a noun. 

Each encoding is a separate table in a valid schema, each with its own validation and verification. You have to stop doing this. This is fundamental!! 
April 29th, 2015 10:06pm

You have audit data (creation and modification) in the row under audit. This is both stupid and illegal. You cannot expose the audit trail to the data user by law.
[Citation needed]
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 1:08am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics