Java Programming with Oracle JDBC

Refining the Analysis

The use of real-world information in the primary key, as we just covered, is what I call a smart key solution. A smart key is a key composed of real-world data values. This is how most entity-relationship analysis was done in the 1980s. We, the programming community at the time, identified a set of entities that organized and described how information was used and how it related to the real world. We used real-world data values as the primary keys for our tables. But this technique of using real-world information to uniquely identify entries was flawed. As with all things, analysts gained experience over time, and with hard-earned experience, learned a better way to define an entity’s primary key.

Defining Dumb Primary Keys

Here’s what we learned. We discovered two flaws when using real-world information in a primary key. First, over time, the users of the applications we built no longer wanted to uniquely identify an entry by the real-world information that had been used. Second, they sometimes wanted to rename the real-world values used in a primary key. Since real-world information was used in primary keys, and therefore was referenced in foreign keys, it was not possible to change this real-world information without a major migration of the data in the database. If we changed a primary key in a row of one table, we had to change it in all the rows in related tables. Sometimes, this also led to major modifications to our applications.

The solution to this ...