Sunday, June 26, 2016

On Kinds of Keys: Natural, Primary and Surrogate Are Sufficient

Revised 7/26/16 (see the Understanding Keys post of 7/31/16 for a more in-depth discussion).

Here is what is wrong with last week's picture painted by Martijn Evers in Kinds of Keys: On the Nature of Key Classifications.

"Many data and information modelers talk about all kinds of keys (or identifiers. I'll forego the distinction for now). I hear them they talk about primary keys, alternate keys, surrogate keys, technical keys, functional keys, intelligent keys, business keys (for a Data Vault), human keys, natural keys, artificial keys, composite keys, warehouse keys or Dimensional Keys (or Data Warehousing) and whatnot. Then a debate rises on the use (and misuse) of all these keys ... The foremost question we should actually ask ourselves: can we formally disambiguate kinds of keys (at all)?"
I, of course, sympathize, as I've deplored the misuse and abuse of terminology in the industry for longer than I care to remember. This is not unique to keys, it's a general absence of foundation knowledge. The answer is yes, we can disambiguate--the key to keys is understanding what in the real world they represent and what database function they fulfill. Things are much simpler than the mindless industry jargon.

A key is a set of one or more relation attributes that uniquely identify tuples, which represents facts about entities in the database. Uniqueness (key) constraints on the relation for every key enforces entity integrity (i.e., there is no duplicate representation of facts about any entity).

Primary Keys

"Of all kinds of key, the primary key and the surrogate key gained the most discussion.If we take a look at the relational model we only see candidate keys a minimal set of one or more attributes that are unique for each tuple in a relation--no other formal distinction is possible. When we talk about different kinds of keys we base our nomenclature on properties and behavior of the candidate keys. We formally do not have a primary key, it is a choice we make and as such we might treat this key slightly different from all other available keys in a relation. The discussion around primary keys stems more from SQL NULL problems, foreign key constraints and implementing surrogate keys."
Codd mandated a primary key (PK) for every relation with multiple keys, mainly for referential purposes (i.e., as target for references by other relations). Obviously, a single key is the PK, but when there are multiple keys, they are candidates and one is selected as PK. The choice is, indeed, not formal (which is why Date downgraded Codd's mandate to just "a good idea"; I would not advise to skip it), but is not arbitrary either: for practical reasons, stability and simplicity are desirable PK qualities.

Entity Properties and Natural Keys

Natural keys (NK) represent in the database entity-defining identifiers (EDI): a set of one or more observable/assertable properties inherent to all entities that defines and uniquely identifies each (e.g., DNA for people, ownership and a set of books for companies). As such, they are meaningful to users and are used for logical data access. EDI's and, therefore, NK's are immutable by definition--they change only if the entities themselves change (e.g., companies involved in mergers, acquisitions, spinoffs)--but tend to be complex (i.e., combinations of attributes), or have complex representations (e.g., DNA). They don't make practical PK's.

Entity Names and Surrogate Keys

When no stable and simple EDI can be identified or explicitly specified in the real world, entities are usually assigned a unique name to substitute for the complex or implicit EDI (e.g., SSN, S/N, patient id), such that all descriptive properties and other names are functionally dependent on it. Entity names are represented in the database by surrogate keys (SK) and all other attributes and keys are functionally dependent on it.

SK's have nothing to do with SQL and NULL's--SQL tables with NULL's are not relations and, therefore, outside the RDM, where all bets are off.

"If we treat surrogate keys as an arbitrary candidate key, we actually decrease the normalization of a data model from, say, 3NF to 2NF. This has led to the special and strict treatment we have for surrogate keys as to prevent this from leading to unwanted issues. Surrogate keys should be seen as not an extra key but as a surrogate for an existing key whose semantics and behavior is strictly regulated by the DBMS system."
As its name implies, SK's are not keys--they do not represent real world properties--but key substitutes that are added to other keys, if any. Since all attributes are functionally dependent on a SK by definition, a surrogate PK does not decrease normalization.
"Business Keys on the other hand are independent keys that are not designated as a surrogate key whose familiarity, uniqueness, visibility and scope span an entire enterprise. Human keys for example are actually not keys in the strict sense, but are non-enforceable candidate keys that heuristically identify tuples, especially those related to real world entities. They are related to the process of relating (UoD) entities to tuples. E.g. using full name and full address to identify and relate a real world person to a tuple representing that person in a database and uniquely identifying that person by a Person ID."
NK's ensure entity integrity and are used for meaningful logical data access. SK's serve as simple and stable PK's for referential purposes. What in the real world do "business keys", or "human keys" represent, what database functions do they fullfill that keys/NK's/PK's and SK's do not and what are their desirable qualities?

Note: An "intelligent key" (also referred to as column overload) is an attribute defined on a non-simple domain with meaningful components. This is hardly intelligent practice (see The First Normal Form in Theory and Practice, Parts 1-3).

Request to my readers (particularly the regular ones): My current writings are in large part dedicated to making accessible to practitioners David McGoveran's interpretation of Codd's RDM, with refinements, extensions and corrections he believes Codd would have ended up with had he lived long enough. In some important aspects there is divergence from the current understanding of the RDM (well, whatever little understanding there is). David will introduce the formal theory in his forthcoming book, LOGIC FOR SERIOUS DATABASE FOLKS. In my writings I strive to make the theory and its practical implications accessible to the thinking data professional--material is not available anywhere else. Please disseminate it by linking to my posts, writings and publications on as many of the web and social media sites for whose audience may deem it of interest. Thanks.

Sunday, June 19, 2016

This Week

1. What's wrong with this picture?

This week's picture is the one of the state of knowledge about keys that Martijn Evers painted in Kinds of Keys: On the Nature of Key Classifications, that I had already commented on. As a result of discussions I've been having with David McGoveran in the context of our forthcoming books (his LOGIC FOR SERIOUS DATABASE FOLKS, my DBDEBUNK GUIDE TO FUNDAMENTAL DATA MANAGEMENT MISCONCEPTIONS), I've decided to rewrite my
comments On Kinds of Keys on the subject. I refer the reader to Martijn's article for a refresher--my rewrite will be posted next week.

2. Quote of the Week

There are no rules of normalization for non-relational databases. Effectively, you start out by denormalizing everything. Which means you're designing the data organization to serve specific queries. So follow the same principle in NoSQL databases as you would for denormalizing a relational database: design your queries first, then the structure of the database is derived from the queries. --Bill Karwin, What is a good way to design a NoSQL database

Sunday, June 12, 2016

Levels of Representation: Conceptual Modeling, Logical Design and Physical Implementation

From last week:

What's wrong with this picture? (Kinds of Data Models,

David Hay: "Part of the ... confusion as to what exactly was meant by “data modeling”--conceptual, logical or physical--is that most data modeling activities seem to focus on achieving good relational database designs ... my approach is the portrayal of the underlying structure of an enterprise’s data--without regard for any technology that might be used to manage it ... a “conceptual data model” ... that represents the business."

Nothing raises uncertainty whether to laugh or cry better than attempts to dispel confusion which suffer from the very confusion they purport to dispel.

Sunday, June 5, 2016

This Week

1. What's wrong with this picture?
David Hay: Part of the ... confusion as to what exactly was meant by “data modeling”--conceptual, logical or physical--is that most data modeling activities seem to focus on achieving good relational database designs ... my approach is the portrayal of the underlying structure of an enterprise’s data--without regard for any technology that might be used to manage it ... a “conceptual data model” ... that represents the business.

Nigel Higgs: ... many folks do not get the difference between the Barker entity relationship style of modeling and the relational style of modeling ... [because] the modeling conventions are very similar and the former [is always] a precursor to RDBMS design.

Clifford Heath: Any terminology for models must project three aspects of intention: (a) audience, (b) level of detail and (c) purpose. These three variables are sufficient to discriminate all the main kinds of models in use. The traditional terms of "conceptual/logical/physical" are manifestly inadequate.

Remy Fannader: Models are meant to describe sets of instances (objects or behaviors).
--Kinds of Data Models,

2. Quote of the Week

The first consideration that needs to be made when selecting a database is the characteristics of the data you are looking to leverage. If the data has a simple tabular structure, like an accounting spreadsheet, then the relational model could be adequate. Data such as geo-spatial, engineering parts, or molecular modeling, on the other hand, tends to be very complex. It may have multiple levels of nesting and the complete data model can be complicated. Such data has, in the past, been modeled into relational tables, but has not fit into that two-dimensional row-column structure naturally. --Jnan Dash, RDBMS vs. NoSQL: How do you pick?