Primary, Surrogate and Business Keys: the Semantics and Syntax of Codd’s Information Principle

Karen Lopez - listmistress@Infoadvisors.com — Wed, 19 Mar 2008 21:13:50 GMT

The session I am attending just after lunch is by Dr. Tom Johnston of Mindful Data. He is focusing on the 5 different types of keys and making assertions about selecting primary keys:

all primary keys should be surrogate keys
all surrogate keys should be globally unique (unique across all rows of all tables)
all surrogate keys should be system generated

He also talked about other types of keys: business keys, foreign keys, and intelligent keys.

He did point out some holes in the Relational Model and Codd's Information Principle, one being that the relational model calls for the mixing of business keys and primary keys in order to support the semantics of the data. As Dr. Johnston points out, this leads to problems when these business keys change (meaning that the semantics are impacted). He believes that Codd's Information Principle will become more obsolete as more formalizations are developed.

He was able to lightly touch on FOPL, a type of formalized predicate logic. I'd like to see a whole day or two on this.

All in all this was a very thought-provoking presentation. I wish there were more time for this topic.

__________________________________________________________

Primary, Surrogate and Business Keys: the Semantics and Syntax of Codd’s Information Principle

Thomas Johnston
Principal Consultant
Mindful Data

Wednesday, March 19, 2008
01:15 PM - 02:15 PM

Level:
Advanced

Business data describe the objects of interest to us; that is their semantics. Primary keys distinguish, and foreign keys refer; that is their semantics. Therefore, to use business data in primary and foreign keys forces that data to play dual semantic roles.

When those roles conflict, we may be forced to change specific values of primary keys and their related foreign keys, or even change their data types and lengths. Often such changes cannot be completed as an atomic transaction. The result is semantic dis-integrity (broken joins), or else a very high cost to avoid it. These costs will be illustrated and explained in this presentation.

Some professionals object to surrogate keys because they believe such keys violate Codd’s Information Principle. Recent comments of Chris Date’s indicate that this is his position. But the Information Principle can be taken as either a semantic or a syntactic principle.
• As a principle about semantics, the Information Principle forbids surrogate keys, and is wrong
• As a principle about syntax, it permits surrogate keys, and is right

Additionally, over the years, data modelers have developed their own practice-based objections to surrogate keys. These objections, too, will be illustrated and discussed in this presentation.

Dr. Tom Johnston received his doctorate in Philosophy from the University of Georgia in 1979. His studies focused on epistemology, ontology, and the philosophy of language. The logician W. V. Quine was a central figure in his doctoral dissertation.

Tom has been working in business IT for over three decades and has worked as a data modeling and data architecture consultant for the latter half of that time.

In the last decade, Tom's publications have focused on improving system and database flexibility by late binding semantics to both data schemas and to code. Tom is currently co-authoring a series of some thirty articles, in DM Review and DM Direct, on how to manage historical data about persistent objects, using today's DBMSs and today's SQL.

Karen Lopez: Musings on Data, Process, and Architecture

Primary, Surrogate and Business Keys: the Semantics and Syntax of Codd’s Information Principle