Simplify database design with abstract keys.(includes related article on programming long integers) (Technology Tutorial)(Tutorial)

Abstract keys simplify database design and help preserve referential integrity even across diverse data sources.

Fred (not his real name) and I had sketched various pieces of his existing systems. I was studying them over lunch. I'll bet you'll recognize your own data architecture in some of these pieces.

Fred has some legacy accounting systems still running under CICS on IBM mainframes. At the other extreme, there are many departmental systems running off spreadsheets. In between, there are a couple of solid client-server systems running on UNIX-based minicomputers. One group is exploring the feasibility of a data warehouse, another team is building Java prototypes for Internet and intranet systems, and a third is looking at three-tier architecture.

Did I mention there's corporate data in every major Xbase flavor, in Access, and even some in Q & A? Fred wanted me to help him make some sense out of the pieces--to fashion some sort of coherent architecture. I helped myself to another spoonful of stew. Then I saw the truth.

Fred doesn't have a data architecture--he has a data stew. A good, rich stew, too: big chunks of this and little bits of that all simmering in a neb worked, e-mailed broth. If you know who to ask in Fred's company, you can get an answer to almost any question by return message. On the other hand, if you don't know everyone there, you're lost.

Out of the stew

Are you caught in your own data stew? This article is about one simple thing you can do for all your data tables that will help you tie pieces together when they need to be tied together. It won't turn stew into a coherent architecture, but it will help you when you need to tie a bit of FoxPro together with some Oracle and a 1-2-3 spreadsheet.

The simple thing you can do is to begin implementing consecutive abstract keys in all your tables. Let's begin with a definition:

An abstract key is one that doesn't contain any data. It's not a last name, or the catenation of two fields. It's a unique value with no meaning, except that within a table, each key is guaranteed to be unique.

I discovered the abstract key in one of Chris Date's academic papers. It's one of those ideas that doesn't complicate your life; it simplifies it--sometimes a lot. You use abstract keys whenever you want to tie one table to another--for example, when the customer's key is entered into the sales record, to tie the sale to a particular customer.

You'll see that consecutive abstract keys are valuable and simple to implement and maintain. For those of you who write C++, you'll also see that they can be handy when you want to search your database without dragging around a large server.

What's so great about abstract keys?

The first advantage of abstract keys is that they're far simpler than other key schemes to implement. Including the time it takes to read this article, a competent programmer can have a consecutive abstract key engine (CAKE) running in-about an hour. Adding proper locking for bullet-proof multi-user operation, a solid CAKE should take about a morning to fully implement and no more than the rest of the day for rigorous testing.

Second, if you ban all other keys from a system, using abstract keys in tables without repeating fields guarantees that your system is in Boyce-Codd normal form (BCNF). The database scientists have proven that BCNF is between third-normal form …