469,341 Members | 6,659 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,341 developers. It's quick & easy.

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

TheSmileyCoder
2,321 Expert Mod 2GB
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=PK_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?
Mar 22 '12 #1
4 3521
Stewart Ross
2,545 Expert Mod 2GB
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
Mar 22 '12 #2
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Mar 22 '12 #3
NeoPa
32,182 Expert Mod 16PB
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.
Mar 23 '12 #4
r035198x
13,262 8TB
Another approach I've seen and used in practice with other databases is two tables:
Expand|Select|Wrap|Line Numbers
  1. LookUpTypes(Id, Name,Locale)
  2. LookUpValues(lookUpTypeId,Id,Name,Value)
  3.  
Mar 23 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by John Collyer | last post: by
2 posts views Thread by DKode | last post: by
2 posts views Thread by CoreyWhite | last post: by
10 posts views Thread by junky_fellow | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.