When is it necessary to add a table in the database for lookup values

Question

just looking for some general direction/best practice about when to use a lookup value table. Lets say I have a table with transactions/payments in it which has the following columns:

transactions

• transaction_id
• order_id
• amount_paid
• payment_status
• payment_type

Ok lets say payment_type can be one of 3 options ('credit card', 'direct credit', or 'international money transfer'). Currently for every table row this payment_type field is being stored as text. Now is it better to store this value as text or make a lookup table for it and store the foreign key back in the transactions table?

E.g.

payment_type

• payment_type_id
• payment_type_name

transactions

• transaction_id
• order_id
• amount_paid
• payment_status
• payment_type_id

Now my thoughts are this field is tightly coupled with the web application, so if more payment types are added in the future, the application will likely have to change to accommodate it. It would be just as easy to add another payment type as text e.g. 'paypal' or add another entry into the lookup table. The various payment types could be stored within the website code in an array.

The benefits I can see of using the lookup table are less overall data in the database (storing a key rather than text). Disadvantages are maybe slightly slower querying as it has to do another join on the data?

So what's the best practice here?

Answer 1

From a performance standpoint, lookup tables are essentially free in most database systems. The SQL analyzer/executor will load up the entire lookup table in memory, if it is small enough.

It's easy enough to add functionality that will allow users or administrators to add more payment types, if that's a requirement.

Answer 2

is is necessary...

I'd say that in this case it is best practice to have the lookup table.

if you have a simple active/inactive or Yes/No column you can just use a char(1) and a check constraint or a bit, but it is best to have a lookup table if you are representing anything more complex.

You can then use this table for the user form input (populating the select box, etc.)

this will flatten and shrink the column, which will allow more rows per page, and help cache memory usage of the main table.

Answer 3

Use a "lookup table" when you need to a) restrict the range of values in a column, and b) change the range of values at run time.

Currently for every table row this payment_type field is being stored as text. Now is it better to store this value as text or make a lookup table for it and store the foreign key back in the transactions table?

You seem to be mixing two different and unrelated ideas.

1. Using "lookup tables" (which I take to be shorthand for "tables and foreign key constraints").
2. Replacing text with id numbers.

To create a "lookup table" to restrict the range of values for "payment_type" in the table "transactions", you can just do this.

create table payment_types (
  payment_type varchar (35) primary key
);

insert into payment_types
select distinct payment_type
from transactions;

alter table transactions
add constraint constraint_name
foreign key (payment_type) 
  references payment_types (payment_type);

When you're talking about monetary transactions, you might not want to cascade either updates or deletes. The risk of great, whopping damage is pretty high.

If you're tempted to reduce the width of the transactions table, consider a small CHAR() code instead of an integer. A code that's human readable avoids a join. In your case, I might use 'cc', 'dc', and 'it'. But I'd only do that after analyzing all the known payment types. (Known to accounting people, not just those known to the database developer, and not just those used in your particular company.)

create table payment_types (
  payment_type_code char(2) primary key,
  payment_type_desc varchar(35) not null unique
);

If a CHAR(2) code won't cover your anticipated values, use an integer, and pay the price of a join to get human-readable values.

Answer 4

I see several benefits in using a reference table:

• Integrity constraint can be applied by the database. Noone can enter, intentionally or by mistake, a 'Playpal' service - unless he has access to the reference table.

• Splitting acces for various applications (or parts) is easier. Some apps has access to transcations table and only read access to the reference table, other (admin panel) can also write to the reference table.

• Smaller overall size of the transaction table.

• Smaller row size of the transaction table.

• In this particular case, the row size is also made constant.

• The various indices that included payment_type now include payment_type_id, so they are smaller.

• The four previous properties may help for faster queries, not slower, as smaller table and index sizes means more data can stay in memory and longer.