Abbreviations in Lookup Tables

When designing a data-heavy application, it's important to be aware of your signal to noise ratio.

For that reason, I'm a big fan of adding abbreviations to lookup tables.  My typical lookup table has the following fields:

• An autonumber ID as primary key
• A 2 to 5-character abbreviation
• A longer full description

Having both a short code and long description gives you a lot of design flexibility.

Providing Some Context

I use three different kinds of forms to represent the main tables in my applications:  

• A read-only lookup form with current row highlighting
• An unbound form to add new records
• A bound form to edit existing records (often a Tabbed Master-Detail form)

On the unbound add form and the bound detail form, I will include combo boxes for the lookup tables that show the full description.  That's because, on those kinds of forms, the goal is to provide clarity for the user.  More importantly, there is enough available screen real estate to do that.

On the read-only lookup form, it's all about maximizing the amount of signal we can provide on a single line of the continuous form.  That's where it's really nice to have an abbreviated field.  You can convey a lot of information in a relatively small area.

Here's a before and after example of what I'm referring to, using Fortune 500 company data from here.

BEFORE

Notice how much space the Industry column takes up.  If you didn't know any better, you would think that was the most important column on the form.

AFTER

The form below would be harder for a new user to understand, as the industry abbreviations will be unfamiliar to them.  But for a regular user, the condensed information would actually make this form easier to use.

Next Steps

In a future article, I'll show you how to automatically generate lookup code abbreviations from existing long descriptions in SQL Server using T-SQL.

Referenced articles

UPDATE [2022-08-15]: Added links to SQL Server "Codify" Function article.