Proper Use of Lookup Tables

Question

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!

Answer 1

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.

Answer 2

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 known values, and can be enforced with a CHECK constraint.
• 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 store 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 than tinyint.

So, to summarise, you use lookup tables:

1. where you have a finite, yet expandable set data in a column
2. where it isn't self-describing
3. to avoid data modification anomalies

Answer 3

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.