My colleagues and I are having a discussion about the normalization of descriptive traits in the database, such as "status" or "type". Let's call the central table of the discussion "Order".

In my regular design-approach, I would define another table, "OrderStatus", to describe the status of an order, and then create a foreign key with a relationship , i.e. "OrderStatusID", on the "Order" table.

This would give me referential integrity. I'd be able to join the status at all times, and my possible values are always present in the "OrderStatus" table.

My colleague doesn't like this degree of normalization, so he'll instead define a varchar field, "OrderStatus" on the "Order" table. This field would contain the values directly.

The possible values of status are defined in his application, more specifically in an Enum of OrderStatuses, and as such, are not available to me unless I have access to the source code of said application.

I'm used to having the entire context of the database exist in the database as relationships and tables, and having to write "WHERE OrderStatus = 'Sold'" as opposed to "WHERE OrderStatusID = 3" bugs me.

What do think? I'm looking for pro's and con's against both approaches, but I'm primarily concerned about performance and readability/maintainability.

2 Answers 2


The design choices you describe are not directly related to normalization.

I agree there should be a lookup table.

I think an OrderStatusID value would increase redundancy. The status (text) value presumably already satisfies many of the qualities of a good key: unique, stable, narrow, familiar to users, etc. Referential integrity can be applied to VARCHAR columns, of course! Each application that uses the key can assign it a enum as required and would be responsible for mapping enum values to status (text) values. This would presumably make the lookup table a single column, 'all-key' table (and therefore would satisfy 6NF, the highest normal form ;)

[If OrderStatusID is an attribute in the Order table then it would not be in 6NF but, as I say, I don't think you are actually asking about normalization at all.]

  • You're right, this has nothing to do with normalization. It concerns me that most people asking database questions seem to think that either normalization must have to do with id numbers or that foreign keys must involve id numbers. Jan 29, 2012 at 21:22
  • I understand that what kind of key to use (int or text) isn't a question of normalization, but basically the question was: use a lookup table or not? In my understanding, that boils down to degrees of normalization. Please correct me if mistaken.
    – Sebastian
    Jan 30, 2012 at 10:42
  • @Sebastian: you are mistaken :) The principle of full normalization and each of the normal forms have formal definitions.
    – onedaywhen
    Jan 30, 2012 at 12:46
  • @onedaywhen Thanks. I'll have to study normalization more than I apparantly have.
    – Sebastian
    Feb 1, 2012 at 10:39
  • @Sebastian: I recommend An Introduction to Relational Database Theory by Hugh Darwen (free PDF download). His approach is to start with 6NF then progress to 5NF then BCNF. The other normal forms, considered historical an no longer useful, are only mentioned briefly.
    – onedaywhen
    Feb 1, 2012 at 10:50

You should use a lookup table

  • Not all clients will use the ENUM in the application
    You will get a reporting or MIS or Excel app connecting at some point
  • How can do "NOT EXIST" otherwise?
    You will be asked this
  • You won't know about client enum changes
  • Strings are inefficient compared to a tinyint, especially when you need to index it for your WHERE clause
  • The data and database will outlive the client application

Also, see these:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.