Mythbuster: Normalizing with Integers is Always Best: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Mythbuster: Normalizing with Integers is Always Best

page 5 of 5

by J. Ambrose Little
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 23789/ 36

Article Contents:

Doggie Bag

[Download Sample]

So what can you take home with you? First, you can take home that some folks can talk about just about anything for a long time. Second, you can take home that, while this may not be busting a myth, it is putting an adage in perspective. What I mean is that while the adage that integer joins are faster than variable character joins is true, that does not necessarily mean you should always opt for creating a lookup table, regardless of what academic rules might dictate.

I would suggest that if the lookup table has only one value in it, that of a semantically pertinent and (de facto) unique value, such as the name of a country, you should seriously consider just duplicating that character data for the sake of simplicity, usability (from a developer and report writer perspective), and performance (remember the last test—if you don’t join, the query is notably faster).

You can still constrain entry to a list. In fact, if you do go ahead and create a separate table to store the list values, you can more easily keep those values in sync even if they change by taking advantage of the cascading update option available in SQL Server. But you don’t have to refer back to the list every time you want to display meaningful data for other tables that use data from your list.

Also keep in mind that if you do need to do a lookup in such cases, it is not all that bad, really, especially if you’re not dealing with many, many rows. If you find that you think the benefits of a semantically meaningful key are worth sacrificing a few milliseconds here and there, go for it.

However, if you do not have such a key for an entity, do not want to rely on users to create one (if that’s an option), or will be dealing with multiple millions of records, you should probably stick with the integer key approach. If you will be replicating a lot, though, you may want to consider using the UNIQUEIDENTIFIER column type for your artificial keys, as that can simplify situations, though it too has drawbacks that we won’t cover here.

« (Page 4)

View Entire Article

User Comments

Title: I am not Against Normalization
Name: J. Ambrose Little
Date: 2005-09-16 9:11:49 PM
Comment:
Give it a rest, folks. If you disagree, you disagree. The point is that it is not an unbendable rule to use artificial integer keys for lookup tables...

Title: Internationalization
Name: ThinCover
Date: 2005-08-10 9:24:25 AM
Comment:
Your example is very poorly chosen. One, a standardized artificial key for country codes, the ISO-3166 standard, already exists. And two, you've made it nearly impossible to internationalize the application since you've stored the country names in English.

Title: Let's address those "downsides" you list
Name: Richard P
Date: 2005-08-09 7:14:04 PM
Comment:
1) Values are not human readable.

Who cares? Why do they need to be stored as human-readable in the DB? That's what the Presentation Layer of your application is for. If your web/GUI/Data Warehouse developer can't join a table with a lookup table, fire them yesterday.

2) Increase in database entities.

Um, no, sorry, you're completely wrong here. You'll need lookup tables as the authoritative "list of valid possible values" anyhow. Or do you advocate not having a list of valid values and just letting every user enter whatever they want ad-hoc? I can't imagine you would. So you have the same number of tables.

3) Added complexity.

Again, I disagree. Some operations that involve displaying the value of the field in the lookup tables are more complex. JOINing and such operations don't give a hoot if you're joining on meaningless integers or character values.

But without meaningless keys, you cannot safely change the display value of the data. If you use the country code instead of a meaningless primary key, you're screwed when, for instance, "Burma" gets changed to "Myanmar". How is "BUR" in a table standing for "Myanmar" human-readable?

4. Replication troubles.

Again, this is a non-issue. Unless someone is talking about "one table for everything" design (in which case they're a complete amateur), you have a list of tables that need to be replicated. You just add the lookup tables to the list.

Finally, you run some benchmarks against values vs. using a lookup table. In 99% of todays applications, you only use the artificial key value in the query anyways.

Take a simple salesman tracking app. User looks up "Joe Smith" and sees he's in the "Southwest" sales region. The "Southwest" region is region 352256 in the DB, but the user doesn't care. He just clicks on the "Find other salespeople in this region" button which then executes

SELECT p.name, p.address, p.phone, p.fax, p.email
FROM salesman s, person p
WHERE s.emplid =

Title: One more thing
Name: BCoder
Date: 2005-05-22 1:50:32 AM
Comment:
It seems that you are thinking about looking at data right out of the database, kind of like it was done a while ago when 90% of the worlds applications where written in COBOL.
Back then ALPHA CODES where in. Just remember that storing INT 150K times saves more space then storing the real name of the country 150K times.

There is a point at which data normalization does not make any sense. That point is when you have a table filled with all keys and no real data at all. That is where there will be real problems for the developer... PersonFirstNameID, PersonLastNameID... It could look this way if there wasnt some balancing done by DBA's.

Title: Something to think about
Name: BCoder
Date: 2005-05-22 1:43:59 AM
Comment:
When not using a lookup table for the country issue presented in this article there are a number of other factors to think about. What happens when one country splits up into 2 or even 3 countries? Let’s just say that it changes its name. The update locks records that do not need to be locked if you store the "name" of the country in the primary table instead of a ID in the column. But I guess that doesn’t matter because I’ve saved my self time and I really don’t care about what slows down an application. When maintaining data this makes a big difference. Another thing that you didn’t talk about was multi users. When a lookup is used a lot it stays in memory and would actually decrease the amount of time it would take because the IO reads would drop...

Are we tracking historical data? historical changes at the record and field level?

Here is something to think about and lookup... when doing a select query add the NOLOCK option to each referenced table... You can also speed up queries even more if you use derived tables....

If it takes me two more weeks to develop some feature or do something a certain way... it is well worth the time especially it I never have to go back and touch it again. It is very selfish to think about the time it would take me to do something one way verse another. In the long run the most approprate way will reduce my overall work effort.

Just think lookups are managed by someone other then a developer and other then a DBA. Let’s keep these people working not cleaning up things that are design flaws.

Think of it this way: If you spend a couple more minutes a day doing something in a different way how much time does that save you in the future when you have to correct previous mistakes, that is if you let your self to make a mistake.

Anyway, in real business world situations where DBA's are designing things and programers do not have any control over this issue, you do what they tell you even if you think they are crazy.

Title: RE: Comments
Name: J. Ambrose Little
Date: 2005-05-20 9:34:32 AM
Comment:
Thanks all for the comments. It's good to have dissenting opinions to encourage people to think critically about issues.

@ svevarn:
Lao, People's Democratic Republic <-- 33 characters long

The 50 character figure was just being generous. Since we're using VARCHAR, it doesn't matter much if we make it max 50 or max 33...

@ Matthew: Thanks for bringing up that point. Cascading updates on keys will only work in database, but I presume a cascaded key update would also replicate, no?

@ Craig: No, not a joke. I'm just suggesting that there are times when using INT keys is less desirable than not.

@ Arturo: When speaking of architecture, things are rarely as black and white as you seem to think. A lookup table, as its name implies, should be used to look up non-key values about an entity. If you really don't have anything meaningful to lookup beyond a name, I'd suggest a lookup table is likely superfluous.

As for your other comments, I can see you have trouble thinking outside the box and reading things in context, so I'll just let what I've said on those subjects stand.

Title: I disagree
Name: svevarn
Date: 2005-05-20 9:18:30 AM
Comment:
"Reduced storage space – standard integer columns are four bytes long. A country name, for example, could be up to 50 bytes long. This is another benefit of normalization in general."

1) Use countrycode "SE" "UK" "US".... char(2) 2 bytes less than int.
2) Name 1 country with more than 30 chars?

natural keys should be used as often as possible. Data should make sense, not look "prettier" just because its an number.

Title: Programmer
Name: Matthew MacFarland
Date: 2005-05-20 5:14:20 AM
Comment:
I agree that subsecond improvements are not important in most cases. The main reason that I use artifical keys is not performance but maintenance. I have used natural keys in the past and these get propogated to other tables as FKs, then users call and want the values updated. In our replicated environment these types of bulk updates are hard to do. Artifical keys shield dbas and programmers from the volitility of user controlled data. Don't believe them when they tell you "this won't change".

Title: Hmmmm
Name: Craig
Date: 2005-05-20 1:52:37 AM
Comment:
I assume this is a late April fools joke.

1. Values are not human-readable. This is a good thing not a bad thing as you state. A key should have not have any business meaning. Period.
2. Increase in total database entities. This is not really an issue IMO. Who cares how many tables you have, it has minimal effect on anything.
3. Added complexity. I think the opposite is true. Having unnormalised data scatter throughout the database is very complex.
4. Replication trouble. What trouble? Just replicate the lookup tables first.

Title: Database normalization
Name: Arturo Martinez
Date: 2005-05-19 4:30:49 PM
Comment:
The purpose of a lookup table in that case is to ensure that each country name is spelled correctly in every place that is used. Period.

Anyone who has had to do maintenance work on database systems could tell you how hard is to work with unnormalized databases. Database normalization is not "theory" and is far from "pedantry": it's a set of necessary steps to obtain a database design that allows for consistent storage and efficient access of data in a relational database that had been proved for years.

"Strangely enough, many developers are still obsessed with performance" What? This should be enough for you to be listed in TheDailyWTF

Title: RE: country abbreviation
Name: J. Ambrose Little
Date: 2005-05-16 3:20:44 PM
Comment:
Hi again,

Yeah, if you want to constrain entry to a list, you will likely want to store the list somewhere, but that's not necessarily a lookup table. If you store the value you need in the records themselves (instead of a lookup key), you won't need to look them up, regardless of if your UI provided a list to constrain entry to.

I don't use abbreviations for countries because most people don't know all the abbreviations, and I'm fairly sure not all countries' postal services understand them and abide by them. It's just safer to use the country name, IMO, to ensure delivery and to make it more readable for users.

Title: country abbreviation
Name: James Curran
Date: 2005-05-16 3:00:10 PM
Comment:
Yes, but in this case (and I grant that this only works with certain specific tables), the abbreviation-as-PK is adequately human-readable, and all that is needed in most of the time, so no lookup is needed. And other times, you can't get away from a lookup table, even when at first you think you can: For example, for user input, you might think you could just enter plain text, but you will need to create a dropdown from the lookup, unless you could accept a wide variety of names for the same country ("US", "USA" "U.S.A.", "United States", "America" etc)

Title: RE: The middle ground...
Name: J. Ambrose Little
Date: 2005-05-16 11:13:09 AM
Comment:
Hi James,

Yes, I didn't address that, but the idea with using the name is, primarily, that no lookup needs to occur for most cases.

Title: The middle ground....
Name: James Curran
Date: 2005-05-16 11:08:44 AM
Comment:
Oddly, you don't mention the primary key that I would use in such a case : the country abbreviation. It has all of the "pros" of an integer PK, while negating 3 of the 4 "cons". Con #4 remains, but I'm not should I'd even qualify that as a "con", rather just the price of data intregrity.