Lookup Table

Question

Lookup Table

melthedba

SSCommitted

Points: 1630
More actions
November 19, 2003 at 12:29 pm

#101762

We currently have about 200+ lookup tables in our database. It is being evaluated to combine all lookup tables into four general tables. I don't like this idea because referential integrity would be much more difficult to maintain, if it could even be maintained.
How are all of y'all implementing lookup tables? Is it one table each for Country and State? Or one table for both with a type to distinguish between whether the record is for a Country or a State?
Thanks!
Melanie

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

I'd say it depends. Mostly we are using one table, but in some cases there is additional data that is associated with the lookup value we usually create separate tables for those.

I'll go against the grain and say that to me, enforcing RI in the db is not a huge priority. If you're going through procs or business objects you should very very rarely violate RI. When/if it does happen, it either means you need a code fix so it doesnt happen again, or the data itself is wrong.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

GRN Hall of Fame Points: 3260 More actions · Answer 2

I will agree with Melanie , other than making RI difficult to maintain , i think it would have some sort of performance impact

a) this table will be heavily used for selects , on a OLTP system you can split the different tables in a frequently used select into different filegroups - so that SQL SERVER can access them more efficiently . With just one table you would not have the option

b) all the indexes will have to be compound - with the extra field added to denote entity types - makes it wider - even if it is just a small char field

c) table scans will be murder - i.e if the query wants all records of a specific entity - the index probably wouldnt be selective enough to use a seek..in which case scanning a few rows is better than thousands.

Finally most of all, it makes the code more manageable - easy to understand schema , no self-referencing relations etc.

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

I also agree with Melanie. As does my friend Joe. In his usual charming style from another list:

quote:
This is a common mistake that Newbies and idiots often make. They literally do not know what a table is. A table is a set of things of the same kind (as per 1NF and DKNF rules), so a nightmare that has sex codes, zip codes, UPC, ISBN, etc. in one place is BY DEFINITION not a table at all. Would you put octopuses and automobiles in the same table?
No referential integrity can be enforced at the database level. Well, not without CHECK() constraints that are HUNDRED OF LINES OF PREDICATES.
More code will be required to enforce the integrity at the application level. And there is no way to be sure that all the applications in the future will get it right!
A query that needs only a few short look up tables that would fit into main storage will suck in the Super-table.
Since some codes are numeric and some are alphabetic, you will be converting datatypes constantly from an insanely long string.
Updating Super-table will lock it and prevent queries from running.
Look ups done on two or more parameters (longitude and latitude, etc.) will require extra columns that the other scalara codes do not use at all, thus wasting space.
The code is unreadable because instead of referring to, say, "sex_code" you have a general format of

SELECT ...
FROM Personnel
SuperTable AS Sexcodes,
SuperTable AS Benefits,
...
WHERE Personnel.sex = Sexcodes.value
AND Sexcodes.code_type = 'Sex'
AND Personnel.benefit_code = Benefits.value
AND Benefits.code_type = 'Benefits'
AND ...;
One screw up and the query is garbage, but it will still run.
If you want to use me as an authority, feel free to do so.
--CELKO--

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Unbelievable, Joe has a friend !!!!

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Just for fun another good one by Joe:

quote:
Please, please, no autographs! I'm only one man, but I try to get
around to everyone. As my wife puts it "You have to slow down, Joe, and
stop along the path of life to stomp the flowers."

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]