Look Out for Look Ups

For a long time now, I've argued that Lookup Fields in tables are One Bad Idea. I've even  Blogged about it.

Recently it occurred to me that maybe it's not enough to TELL people Lookups are almost always bad. There's  a world famous page that offers eight significant problems with Lookups. My complaints are actually focused on the fallout from Number 6 on that list. Lookup fields in tables simply make it far too easy for inexperienced users to screw up royally.

Let's look at just one example, which is all too common. First I'll show how it should be done, if you really, really just have to do it yourself. The following discussion is based on a three table database of employees, departments, and employee assignments to departments. You can find the originals of these tables, btw, on GPCData.com

The first table, shown in design view and datasheet, is pretty straightforward, a Primary Key, a native key used by the company to identify departments, and the name of the department. No surprises here.

Department Table in Design View

Department Table in Datasheet View

In the next two screenshots, you see the employee table in both design view and datasheet view. Again, nothing out of the ordinary here. 

Employee Table in Design View
 

Employee Table in Datasheet View

In the next two screenshots things get interesting. This table is a junction table that sits between employees and departments to track the history of department assignments. It's a history table because a department can have one or more employees and an employee can be assigned to different departments at different times during the course of their employment. Look at the first screenshot, and then I'll explain how it was set up.

Employee Department History Table in Design View.



Because it's a history table it has from and to dates, in addition to the foreign keys that "point back" to an employee and to a department.

The first four fields in this table are really all that is needed. The last two fields, EmployeeIDLKP, and DepartmentIDLKP are only there to illustrate why Lookup fields in tables are One Bad Idea.

The primary key on this table is actually a compound key on EmployeeID, DepartmentID and FromDate. That allows us to designate a unique combination for each assignment. Combined with other rules, it keeps us from duplicating assignments inappropriately, while allowing overlapping assignments. That's all outside the scope of the current discussion though.

So, as you know. This design stores PKs from department and employee tables exactly as they appear in the parent tables with no ambiguity,

I added two lookup fields  to illustrate how they work. These are, btw, designed so that the proper PKs are stored as FKs in the look up fields. The screenshot above shows the row source for the employeeID. It is the first column in the row source, which is also hidden. That means when we flip this table to design view, we'll see the employee name, not their PK. A win for the person who wants to see the name in a table.

Employee Department History in Datasheet View

 

Here we see that the Employee Lookup field not only displays a value, it actually displays a concatenated value, "LastName, FirstName". How cool is THAT? The department Lookup field shows, not the department name, but the text value for the department Number. Hm. That's kind of cool, too, but doesn't it raise a question about WHAT you're really planning to store here? is it really the (appropriate) DepartmentID Primary Key, or the (inappropriate) Department Number, which is not a Primary Key.

 

No crisis yet, but this sort of ambiguity is precisely why I object so strenuously to lookup fields in tables. Even when used correctly, they throw a layer of ambiguity on top of the process, and that's something we can all do without.

 

In future installments, I'll go beyond this initial problem and show you even more insidious problems caused by Lookup fields in tables. Trust me, it gets worse than relatively harmless ambiguity, much worse.