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 1 of 5

Published: 16 May 2005

Unedited - Community Contributed

Abstract
It is common sense that using artificial integer keys and lookup tables are a best practice for data normalization, but does that dictum hold true under fire? Read more to find out.

by J. Ambrose Little
Feedback

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

Article Contents:

In the Beginning

[Download Sample]

For as long as I’ve been in development (which is, admittedly, not as long as many), it has been an oft-repeated adage that you should not use variable character (VARCHAR) columns as primary keys in database tables. No, rather, you should use integers, they say. And most databases that I’ve run into in my career follow this advice, having almost exclusively integer (INT) auto-increment (IDENTITY) artificial primary key columns—the good ol’ “Id” (or “ID,” if you prefer) column. There are exceptions, of course, but by and large, this has been the design approach that appears to be most common, at least in my experience.

The problem with this approach, barring disputes about natural versus artificial primary keys, is that you start creating a multitude of lookup tables to house one value just for the sake of consistency and, theoretically, speed. For instance, let’s say you have a table called Orders that has a column that should store the country whence you need to ship the items in the order, and you need to constrain entry to a list of real countries for the sake of consistency.

Adhering to the aforementioned adage, you would likely then dutifully create a Countries table with Id and Name columns (and possibly one or more abbreviation columns). In your Orders table, you might create a CountryId column to store a key reference to the appropriate row in the Countries table. You would then proceed to build a stored procedure to retrieve a list of countries to use in, for example, an ASP.NET DropDownList control, using the Name for the DataTextField and the Id for the DataValueField.

For experienced data-oriented developers, this scenario will have a soporific effect because you’ve done it so many times. I would also wager that most of us would agree that it is the best approach. But is it? Let’s reason together about it.

View Entire Article

(Page 2) »

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.