IDs in Lookup tables

[Zydeceltico]

Hi All -

General best-practices question. I have quite a few lookup tables. Currently each of the lookup tables has at least two fields: the first being an autonumber PK and the second being the recorded value.

For example tblGauge that has two fields the PK field has these values: 1,2,3,4,5,6,7,8. The Gauge field has these values: 20, 19, 18, 17, 16, 15, 14, 13. These are material thicknesses that we use in products. This is a pretty finite list that won't change much if ever. Is this best practice? I'm thinking it may not be. This may be best used via a Value List right on the form.

Another lookup table is called tblMaterialCoatings. This table has the autonumber PK but the second field holds a string value (e.g., galvanized, coil coated, zinc phosphate, etc). This table of values is different in that, firstly it is strings and more importantly the values might be added to or subtracted from over time.

So am I wondering: 1) if I should do away with the PKs in both, either, or neither table and make "looking up" simpler in a combo box for instance and 2) in tblMaterialCoatings if I do away w/ the PK and just use the string values, I will be recording some string value in a field in my main table (instead of a Long) every time I enter a new job and I am wondering if that could be an issue later. I don't really see how - - but I am also less experienced than many of you.

Thoughts?

Thanks!

Tim

 

[theDBguy]

Hi Tim. I usually consider using a value list only if there were only two (like Yes or No) or maybe up to three options. Otherwise, I will use a lookup table with a primary key whether the list is fixed or not. Consider the following example, let’s say you have a list of 10 fixed options and decided to use a value list. Now, you will have to type those 10 options for every combobox on every form where you might want to refer to these choices. Sounds like it could be a lot of work to me.

 

[Zydeceltico]

Hi Tim. I usually consider using a value list only if there were only two or maybe up to three options. Otherwise, I will use a lookup table with a primary key whether the list is fixed or not. Consider the following example, let’s say you have a list 10 fixed options and decided to use a value list, now you will have to type those 10 options for every combobox on every form where you might want to refer to these choices. Sounds like it could be a lot of work to me.

Yeah - it does seem like my lookup tables would be far easier to manage.

 

[essaytee]

What @theDBguy said. OP, in your situation I would still have Lookup tables but I would not refer to their PrimaryKeys but to the actual values. This has a side benefit of not needing to include the Lookup tables in other queries, the data is contained in the main table. Simplifies things somewhat.

Something to think about regarding Lookup tables or any tables that are looked up, are the historical implications. Consider this, you have a table of Names and other tables. Now in the other tables where Names are included, rightly so, they are looked up, their primary key is inserted in the foreign table. When viewing these foreign tables, the Names are easily referenced.

Now, what happens if a Name record is changed, that is, Miss Jane SMITH becomes Mrs Jane BROWN. That name is changed, no problems with that. What are the implications though? If perusing your other tables and viewing a record from 2 or 3 years earlier, before the name change, your record will indicate the changed name, BROWN, and not SMITH. All I'm saying is be aware of this.

 

[The_Doc_Man]

A lookup via combo box or list box is the best way (you DID ask for best practice) for looking up something from a finite but not necessarily permanently constant list. I.e. if you can or subtract or modify in this list, use a lookup table.

But there is another consideration. If you EVER want to build a report and used a coded lookup, you will need that table for the report be able to translate the code into whatever value was used.

And here, you suddenly find that the PK is indispensable because you will need to do that via some form of JOIN - and you can't do that without having a PK on the code column of the lookup table.

While I understand TheDBGuy's position and don't necessarily disagree with him, there is ALWAYS the question of WHAT ELSE will you do with that translation? Where else will you need to perform a translation? Before getting rid of even SHORT tables, consider whether you need to use the translation in more than one place. Because having that table saves a ton of later translations.

essaytee also brings up a point to consider. What if the description field has to change yet the code stays the same? (For inventory items, shouldn't happen - but for personnel ID lookups, might WELL happen.) With a separate table for lookups, you can include a pair of date fields between which the translation is valid. And you can use a dummy date to represent the indefinite future if the "expired" date is really a Date field. You can legally put a date of 31-Dec-9999 and Access WILL handle that correctly.

Of course, if you do use that date, you are in essence saying "Fixing this date will be someone else's problem." Though it is a sobering, perhaps even chilling thought that Access and Windows would be around that long. I shudder to think about it.

 

[Zydeceltico]

Truly grateful for ya'll.

Just the insight I was looking for.

Thank you!

Tim