By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,588 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

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

TheSmileyCoder
Expert Mod 100+
P: 2,321
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
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
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
Expert Mod 100+
P: 2,321
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
Expert Mod 15k+
P: 31,186
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

10K+
P: 13,264
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.