Discussion: Small Lookup tables vs value list vs ...

[TheSmileyCoder]

Today I found myself once again in the process of adding a small lookup table with only 3 values, as shown below:
Fields:
PK_Importance (Autonumber)
tx_Importance (Text,10)

Values:
1 - High
2 - Medium
3 - Low


I will then use the table as recordsource for a combobox bound to ID_Importance in my main table, and use it in reports by joining on ID_Importance=P K_Importance to bring in the tx_Importance into my recordset.

I just stopped today, and wondered if this is the best way to do it. I realise this to some degree depends on whether or not the table values are expected to be changed or more values added, as well as the number of initial values.


I personally find this system quite easy to maintain, and should I ever need to add in an extra value its easy as pies. However a thing I was wondering is whether this approach has performance issues when running reports, where the join is used.

This was a bit unstructured I realise, basicly just me wanting to start a discussion on a subject in which there is many different equally valid ways to progress. How do you guys do it?

Do you use a value list? If so, why?
Do you skip the ID field (PK_Importance) and simply directly use and store the value field? If so, why?

[Stewart Ross]

I tend not to use value lists at all, as I think these are much less easy to maintain than lookup tables. Value lists are buried away in the properties of a control, whereas tables are much more visible. Tables also allow explicit 1-m relationships to be set and enforced.

I have recently started to use the text value directly as the primary key of the lookup table instead of using an indirect numeric code. This does away with the need to join the many-side table back to the one-side lookup for reporting purposes, for example. I doubt that performance of the joined main+lookup table combination is any issue at all when considering typical lookup tables with few rows - anyhow that's not why I started to use the text value directly as the PK.

In my lookup tables I frequently add a sort order field to allow for custom ordering of the lookup values in a combo box, say. It could be argued that the sort-order field is just an alias for an alternate key, but it's not how I think of its use.

I always set explicit relationships between the lookup tables and those in which they are used, to ensure that the values stored are relationally valid.

-Stewart

[TheSmileyCoder]

Another scenario. I have a combobox in which the user can select a percentage. To keep it simple, the restriction is that he can only choose 10%, 20%, 30% and so forth.

Would you still use a lookup table for that? That is a place where think a value list is appropriate.

[NeoPa]

I typically use tables for such things. I don't believe the performance issues are noticeable unless the table is particularly large, in which case it is even more important than otherwise.

[r035198x]

Another approach I've seen and used in practice with other databases is two tables:

LookUpTypes(Id, Name,Locale)
LookUpValues(lookUpTypeId,Id,Name,Value)