Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Jan 26 '12 at 11:56

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 5 down vote accepted

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.]

share|improve this answer
    
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. –  Mike Sherrill 'Cat Recall' Jan 29 '12 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 '12 at 10:42
    
@Sebastian: you are mistaken :) The principle of full normalization and each of the normal forms have formal definitions. –  onedaywhen Jan 30 '12 at 12:46
    
@onedaywhen Thanks. I'll have to study normalization more than I apparantly have. –  Sebastian Feb 1 '12 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 '12 at 10:50
show 2 more comments

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:

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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