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.

I'm having trouble figuring out exactly how to place good boundaries for when and where to use lookup tables in a database. Most sources I've looked at say that I can never have too many but, at some point, it seems like the database would be broken down into so many pieces that, while it may be efficient, it is no longer manageable. Here's a thrown together example of what I'm working with:

Let's say I have a table called Employees:

ID  LName   FName   Gender  Position
1   Doe     John    Male    Manager
2   Doe     Jane    Female  Sales
3   Smith   John    Male    Sales

Pretend for a moment that the data is more complex and contains hundreds of rows. The most obvious thing I see that could be moved to a lookup table would be Position. I could create a table called Positions and stick the foreign keys from the Positions table into the Employees table in the Position column.

ID  Position
1   Manager
2   Sales

But how far can I continue to break the information down into smaller lookup tables before it becomes unmanageable? I could create a Gender table and have a 1 correspond to Male and a 2 correspond to Female in a separate lookup table. I could even put LNames and FNames into tables. All "John" entries are replaced with a foreign key of 1 that points to the FName table that says an ID of 1 corresponds to John. If you go down this rabbit hole too far like this, though, your Employees table is then reduced to a mess of foreign keys:

ID  LName   FName   Gender  Position
1   1       1       1       1
2   1       2       2       2
3   2       1       1       2

While this might or might not be more efficient for a server to process, this is certainly unreadable to a normal person who may be trying to maintain it and makes it more difficult for an application developer trying to access it. So, my real question is how far is too far? Are there "best practices" for this sort of thing or a good set of guidelines somewhere? I can't find any information online that really nails down a good, useable set of guidelines for this particular issue I'm having. Database design is old hat to me but GOOD database design is very new so overly technical answers may be over my head. Any help would be appreciated!

share|improve this question
2  
Using "lookup" tables is one thing. Replacing text with id numbers is a completely different thing. –  Mike Sherrill 'Cat Recall' Dec 12 '11 at 3:04
    
Gender may not always be fixed to 2 values! Now that we have gender transitions, who is to say an application may not need additional categories like 'born male now female' or 'born female now male'. –  user24664 Jun 6 '13 at 19:19
    
@Mike, good comment! –  Walter Mitty Jun 6 '13 at 19:23
    
In my shop the thinkers were able to stop after only four choices, male, female, transgender, would not disclose. –  kevinsky Jun 6 '13 at 19:34
add comment

3 Answers 3

up vote 11 down vote accepted

But how far can I continue to break the information down into smaller lookup tables before it becomes unmanageable? I could create a Gender table and have a 1 correspond to Male and a 2 correspond to Female in a separate lookup table.

You're mixing two different issues. One issue is the use of a "lookup" table; the other is the use of surrogate keys (id numbers).

Start with this table.

ID  LName   FName   Gender  Position
1   Doe     John    Male    Manager
2   Doe     Jane    Female  Sales
3   Smith   John    Male    Sales

You can create a "lookup" table for positions like this.

create table positions (
  pos_name varchar(10) primary key
);

insert into positions
select distinct position 
from employees;

alter table employees
add constraint emp_fk1
foreign key (position) 
  references positions (pos_name);

Your original table looks exactly like it did before creating the "lookup" table. And the table of employees requires no additional joins to get useful, human-readable data out of it.

Using a "lookup" table boils down to this: Does your application need the control over input values that a foreign key reference provides? If so, then you can always use a "lookup" table. (Regardless of whether it uses a surrogate key.)

In some cases, you'll be able to completely populate that table at design time. In other cases, users need to be able to add rows to that table at run time. (And you'll probably need to include some administrative processes to review new data.) Gender, which actually has an ISO standard, can be completely populated at design time. Street names for international online product orders probably have to be added at run time.

share|improve this answer
    
I didn't know you could do all that! The way your method works is kinda beautiful. Thank you! –  Brad Turner Dec 15 '11 at 18:03
    
+1 for ISO standard –  Neil McGuigan Apr 28 '13 at 8:33
    
I joined DBA Stack Exchange just so I could vote this answer up. This is beautiful and never occurred to me. Thanks! –  CindyH Apr 29 '13 at 15:29
add comment

The answer is a "it depends". Not very satisfying but there are many influences pushing and pulling the design. If you have app programmers designing the database a structure like you describe works for them because the ORM hides the complexity. You'll be pulling your hair out when you write reports and have to join ten tables to get an address.

Design for the use, intended use and likely future use. This is where your knowledge of the business process comes in. If you are designing a database for a veterinary business there are reasonable assumptions about size, usage and directions in functionality that will be quite different than a high-tech start-up.

To reuse a favourite quote

"A wise man once told me "normalize till it hurts, denormalize till it works".

Somewhere in there is the sweet spot. My experience has been that having a key id in more than one table is not as serious a crime as some think if you never change primary keys.

Take this abbreviated example of highly normalized tables from a real system

CREATE TABLE PROPERTY
(ID                          NUMBER(9)           NOT NULL);

CREATE TABLE PROPERTY_TYPE
(ID                          NUMBER(9)           NOT NULL);

CREATE TABLE PROPERTY_LOCALE 
PROPERTY_ID                  NUMBER(9)           NOT NULL,
(LOCALE_ID                   NUMBER(9)           NOT NULL,  --language 
VALUE                        VARCHAR2(200)       NOT NULL);

CREATE TABLE PROPERTY_DEPENDENCY
(PROPERTY_ID                 NUMBER(9)           NOT NULL,
 PARENT_PROPERTY_ID          NUMBER(9)                   ,
 PROPERTY_TYPE_ID            NUMBER(9)           NOT NULL);

These tables set up a linked list of single properties and parent child properties and they get used here

  CREATE TABLE CASE_PROPERTY
  (ID                        NUMBER(9)           NOT NULL,
  PARENT_ID                  NUMBER(9),
  CASE_ID                    NUMBER(9)           NOT NULL,
  PROPERTY_ID                NUMBER(9),
  PROPERTY_TYPE_ID           NUMBER(9)           NOT NULL);

This looks fine: get all the cases with a property_id in one select

Let's get a list to pick from

 Select pl.value, pd.property_id
 from property_locale pl, property_dependency pd
 where pl.property_id = pd.property_id
 and pd.property_type_id = 2;  --example number

Now try selecting all the properties of a case if it has property_types of 3 and 4 and 5, or not...

SELECT   cp2.case_id,
         (SELECT   pl.VALUE
            FROM   case_property cp, property_locale pl
           WHERE       cp.property_id = pl.property_id
                   AND CP.PROPERTY_TYPE_ID = 2
                   AND pl.locale_id = 2
                   AND cp.case_id = cp2.case_id)
            AS VALUE1,
         (SELECT   pl.VALUE
            FROM   case_property cp, property_locale pl
           WHERE       cp.property_id = pl.property_id
                   AND CP.PROPERTY_TYPE_ID = 34
                   AND pl.locale_id = 2
                   AND cp.case_id = cp2.case_id)
            AS VALUE2,
         (SELECT   pl.VALUE
            FROM   case_property cp, property_locale pl
           WHERE       cp.property_id = pl.property_id
                   AND CP.PROPERTY_TYPE_ID = 4
                   AND pl.locale_id = 2
                   AND cp.case_id = cp2.case_id)
            AS VALUE3
  FROM   case_property cp2
 WHERE   cp2.case_id = 10293  

This just hurts...even when you use more elegant ways of dealing with this. However, add a bit of de normalization by breaking out properties that a case will have only one property_id for and this could be much better.

To find out when you have too many tables or not enough try querying the database with questions the application, a report and a year to year analysis will use.

share|improve this answer
3  
ID numbers don't have anything to do with normalization. Just because every table has an id number doesn't mean it's in 5NF, or even in 3NF. It just means that you have to do a lot of joins to get usable data out of that table. –  Mike Sherrill 'Cat Recall' Dec 12 '11 at 11:54
add comment

In your Employees table, I'd only have a lookup for "Position" because it a limited set of data that can expand.

  • Gender is self describing (say M or F), limited to 2 values, and can be enforced with a CHECK constraint. You won't add new Genders (ignoring political correctness bollocks)
  • The first name "John" isn't part of a limited, restricted set of data: the potential set of data is massive to the point of effectively limitless so it shouldn't be a lookup

If you want to add a new Position you simply add a row to the lookup table. This also removes data modification anomalies which is one point of normalisation

Also, once you have a million employees then it's more efficient to stored tinyint PositionID than varchar.

Let's add a new column "salary currency". I'd use a lookup table here with a key of CHF, GBP, EUR, USD etc: I wouldn't use a surrogate key. This could be restricted with a CHECK constraint like Gender but it is a limited yet expandable set of data like Position. I give this example because of I'd use the natural key even if it does appear in a million rows of employee data despite being char(3) rather then tinyint

So, to summarise, you use lookup tables

  1. where you have a finite, yet expandable set data in a column
  2. where is isn't self describing
  3. to avoid data modification anomalies
share|improve this answer
1  
One possible reason to put gender into a lookup table is localization. –  a_horse_with_no_name Dec 12 '11 at 15:01
    
"Gender... (say M or F), limited to 2 values... ignoring political correctness bollocks" -- Ironically, it is the very same political correctness that you seem to abhor that causes people to incorrectly "gender" ('Masculine', 'Feminine') when they mean "sex" ('Male', 'Female'). If the context is grammatical gender then there are usually more than two values. If the context is recording the sex of a newborn then there is at least four values ('has not been officially assessed' and 'official assessment was inconclusive'). p.s. I don't mean to sound harsh, I enjoyed the irony :) –  onedaywhen Jan 26 '12 at 12:46
2  
@onedaywhen: the correct value for a column called "Sex" is "Yes please". Unless you're British –  gbn Jan 26 '12 at 12:52
    
The term "anomolies" is being misused here since the term has a different particular meaning related to normalization, and the link is inappropriate. –  philipxy Jul 17 at 22:54
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.