One true Lookup table advantages vs. disadvantages [closed]

Question

So I've read enough to know that having one lookup table isn't the best way to go. From a performance standpoint, why is that? Consider the following example:

you have a list of schools in cities. There can me more than one school in a given city. So you have a junction table schoolCities between the two. Now consider a list of businesses. there can be more than one business in each city. So you have a junction table businessCities. The question really is: why is it so bad if you just use one cities table and make a junction table for schools and business versus having each table school and business its own copy of the cities table?

Answer 1

Actually, there can be some advantages to OTLT design (I literally feel heads exploding right now).

The model that I've seen work really well would be something like this.

LookupId, LookupTypeId, LanguageId, Description (the displayed data)

Then your params in the stored proc. would be just the typeid (to get all that type). If you just needed to transform the Id of a particular type, you'd just pass the LookupId. The default language would be set (in our case, it was English). This was a great way to skin the site's display data in a multi-language format.

We actually used it a Match.com and supported several million users per day on it very well. We literally just cached the lookups at web startup. It was a very efficient system. The biggest advantage was that we could literally just push data to production instead of big table schema additions / changes.

The way I look at it, as long as you're only dealing with small drop down data, etc. (typical application display stuff), it can offer big advantages in terms of performance, maintenance and ease of adding on new types.

I know it's an unpopular methodology.. However, it does work well in the right scenarios and if your use of it is limited AND disciplined.

Just my 2 cents. I'm sure I'll have points deducted or whatever for taking an unpopular stand on the topic though.

Answer 2

What you're describing isn't the "one true lookup table;" a Cities table in your example sounds like a good normalizing idea. The "one true lookup table" would be more like

CREATE TABLE TheOne (
    LookupType varchar(20) NOT NULL -- City, color, shoe size...?
    , LookupValue varchar(1000) NOT NULL -- how big does it have to be?
    , CONSTRAINT pk_TheOne PRIMARY KEY (LookupType, LookupValue)
)

and you might have values:

'City', 'Philadelphia'
'City', 'St. Petersburg'
'City', 'Paris'
'State', 'Pennsylvania'
'State', 'Florida'
'Country', 'United States'
'Country', 'France'
'Brand', 'Lucky'
'Brand', 'Diesel'
'Brand', 'Old Navy'

and so on. Pure evil.

A Cities table would probably associate a city with a State or other parent geographical region, since there can be many different cities with the same name (Paris, Lima, Levittown, etc).