Common lookup table usage

Gagnon · #1 (**permalink**) 03-18-08, 16:29

I am curious if this is a common practice and what the best practices are for maintaining referential integrity while using a common lookup table.

I have always used a common lookup table wherever I have worked and now that we are replicating to multiple environments it makes maintenance much easier having to replicate only 1 lookup table that contains ~90% of our lookup data.

Here is a decent description of what a common lookup table is:
http://www.projectdmx.com/dbdesign/lookup.aspx

Here is some sample DDL to help formulate a discussion on this topic:
CREATE TABLE CODE
(
CODE_ID INT -- PK
,CODE_TYPE_ID INT
,CODE_NAME VARCHAR(250) -- LOOKUP TEXT
)

CREATE TABLE CITY
(
CITY_ID INT -- PK
,STATE_ID INT -- FK to CODE table of CODE_TYPE_ID = 1
,CITY_NAME VARCHAR(250)
)

Now obviously we could create a simple FK from CITY.STATE_ID to CODE.CODE_ID what we are lacking is constraining it to only the values with CODE_TYPE_ID = 1 (since this is a common lookup table we can store other values in the CODE table unrelated to States)

I can think of two solutions to this, the first solution is to include a CODE_TYPE_ID in my CITY table and have a value of 1 in every single row (I know bad DB design), but this would allow me to create a compound FK that would match on both columns and this would work, albeit an ugly solution.

Alternatively for solution #2 I could create an INSERT/UPDATE trigger on CITY to ensure the range of STATE_ID is exactly one of the values stored in my CODE table for CODE_TYPE_ID = 1.

Any other solutions out there? How do others handle this problem?

Gagnon · #2 (**permalink**) 03-18-08, 16:35

Solution #3, create a UDF + Check Constraint combo to enforce RI:

Code:

CREATE FUNCTION [dbo].[UDF_CHECK_CODE_ID](@CODE_ID INT, @CODE_TYPE_ID INT)
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = 0
   SELECT @retval = 
(
	SELECT 1
	FROM CODE C
	WHERE
		C.CODE_ID = @CODE_ID
	AND	C.CODE_TYPE_ID = @CODE_TYPE_ID
)

   RETURN ISNULL(@retval, 0)
END;

Code:

ALTER TABLE [dbo].[CITY]  WITH CHECK ADD  CONSTRAINT [CK_CITY_CODE] CHECK  (([dbo].[UDF_CHECK_CODE_ID]([STATE_ID], 1)>(0)))
GO
ALTER TABLE [dbo].[CITY] CHECK CONSTRAINT [CK_CITY_CODE]

Pat Phelan · #3 (**permalink**) 03-18-08, 16:53

Ah yes, we discuss OTLT every few months. There are a few folks that don't outright abhor it, and OTLT is quite appealing until the fifth night you are cursing it at 04:00... From that point on however, it quickly loses its luster!

-PatP

blindman · #4 (**permalink**) 03-18-08, 18:40

What you are referring to is know, somewhat mockingly, as an OLTP, or "One True Lookup Table":
http://www.google.com/search?source=...=Google+Search
It is a concept which seems neat and tidy, and which eventually every DBA stumbles across and thinks they have come up with an original idea, and yet it has many hidden and subtle drawbacks.
Personally, I no longer even believe in lookup tables. See my post in this thread, which has lots of other informative posts as well:
Lookup tables (aka domain/reference/code tables) surrogate vs natural keys?

LoztInSpace · #5 (**permalink**) 03-18-08, 19:32

Quote:

Originally Posted by Gagnon

Any other solutions out there? How do others handle this problem?

Yes - do it properly with one table per type. Jeez. How hard is it? What are the downsides?

loquin · #6 (**permalink**) 03-18-08, 19:40

Just look at the author's conclusion in the article that you linked.

Quote:

Originally Posted by LINK

Conclusion

Whether it is used as a short term make shift solution or as a long term viable solution, common lookup tables have no place in sensible database design. While application enforced integrity is often touted by many in the developer crowd, the fact that DBMS is the centralized enforcer of all integrity constraints still stands. In that regard, considering preserving data integrity and logical correctness as the foremost goal in a given database design, common lookup tables are one of the worst kind of mistakes that one can make in a stable data management solution.

Gagnon · #7 (**permalink**) 03-18-08, 23:51

Pat - thanks for the link, I read the entirety of it, man the OP got flogged there. I am not saying he didn't deserve a lot of it

blindman - by no means did I think this was a unique idea, I have seen it at old jobs and at my current job, to undo it would be quite the undertaking. Not so much the creating of 87 distinct lookup tables so much in having to refactor all of the procs, ASP and .Net code that currently utilize this table. I also read your post regarding there not being any true lookup tables anymore and I totally agree in a well designed database. One thing my company is shifting towards is removing a lot of business logic from the DB so these additional attributes (like "in progress" that you mention) will be encapsulated in the business component code. I am not so sure I agree with that line of thinking, but I am outnumbered at this point.

Without opening up a whole new can of worms, I do have one question:

Couldn't a poor man's RI be accomplished by creating a UDF to check against the range of values for the given "type" and then include a call to the UDF in a check constraint? I don't believe this would work if that column allowed nulls but otherwise it seems pretty solid in maintaining RI.

One more quick thing to add, at the current firm I am at for every new project we work on there are probably about 5-10 requests for new lookup tables so part of the allure of using the OTLT is consolidating this static data in 1 table since this will likely get replicated to two other DB's (as compared to creating/maintaining 15-30 new tables per project (5-10 times 3)).

pootle flump · #8 (**permalink**) 03-19-08, 05:40

Quote:

Originally Posted by blindman

What you are referring to is know, somewhat mockingly, as an OLTP, or "One True Lookup Table":

What is it with you people?

OLTP <> OTLT

mike_bike_kite · #9 (**permalink**) 03-19-08, 06:27

Quote:

Originally posted by Gagnon
I read the entirety of it, man the OP got flogged there

In return I've decided to make it my life's work to annoy the hell out them as much as I can

Must admit you deserve a prize for getting to the end of that thread though.

The fact you RI was your main concern indicates you shouldn't use OTLT. I usually use it simply because of the simplicity it lends to the overall design. I tend to see more issues with overly complex designs being used poorly by developers cause no-one can understand the DB and with the values in the lookups being years out of date cause the original designer left and no-one wrote screens to update these tables. Most people here seem to have more issues with bad data and so swing the other way. YMMV.

Thrasymachus · #10 (**permalink**) 03-19-08, 08:33

I (the overpaid contractor who they pay for DB Design input) am still losing the argument against OTLT and EAV here at the current gig. Their employee DB professional sent me some mangled queries to tune yesterday on a database she is currently building with an OTLT design. And what do they have me doing instead of database design... C# and XML after I have repeatedly shown them the errors in their ways.

Oh well. I am no no DB jihadist. If they want to keep paying me to clean up messes and put band aids on boo boos they create, I am fine as long as the checks keep clearing.

Thrasymachus · #11 (**permalink**) 03-19-08, 08:35

Quote:

Originally Posted by mike_bike_kite

The fact you RI was your main concern indicates you shouldn't use OTLT.=

sweet Jesus in a hottub. When is data integity not the number one concern?

mike_bike_kite · #12 (**permalink**) 03-19-08, 09:22

Quote:

Originally posted by Thrasymachus
I (the overpaid contractor who they pay for DB Design input)

I preferred you when you were the suicidal philosopher living in a single room

Quote:

am still losing the argument against OTLT ... here at the current gig

hopefully if you come up with a winning argument at work then you'll be kind enough to post it here.

Mike

Thrasymachus · #13 (**permalink**) 03-19-08, 09:29

Quote:

Originally Posted by mike_bike_kite

I preferred you when you were the suicidal philosopher living in a single room

hopefully if you come up with a winning argument at work then you'll be kind enough to post it here.

Mike

you can not win arguments against invincible ignorance with you or my boss.

I have 2 rooms, plus a kitchen and a bathroom thank you very much and I am not suicidal, nor have I ever been because my life is not mine to take. That ultimate act of selfishness is a crime to all of those that you share your life with as well as any higher power you may or may not accept in your life.

blindman · #14 (**permalink**) 03-19-08, 09:29

Quote:

Originally Posted by pootle flump

What is it with you people?

OLTP <> OTLT

Making fun of someone's dyslexia is bad enough, but mocking a blind man's dyslexia is really cruel.

Quote:

Originally Posted by Gagnon

One thing my company is shifting towards is removing a lot of business logic from the DB...

Oh God. Which "business logic" are they removing? Data-based business logic or user-interaction business logic?
How can developers be so stupid about this stuff?

Pat Phelan · #15 (**permalink**) 03-19-08, 10:43

Quote:

Originally Posted by blindman

How can developers be so stupid about this stuff?

You're implying that there is some kind of limit ???

Developers get paid to develop code, so they value things that make it quick and easy to produce code.

DBAs get paid to get the data correct, so they value things that enforce correctness even if that comes at the expense of a bit more coding time.

Businesses get paid to do business. Businesses that succeed in the short run value developers that code quickly because that leads to shorter time to market. Businesses that succeed in the long run look at the big picture and consider the total cost of operations, so they value getting things done right the first time.

Three different perspectives, three different sets of values, three different sets of behavior. Voila!

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech