In a comment from yesterday’s editorial, Kenneth Ambrose writes, “it’s quite a sad commentary on our industry that 90% of the people defining and implementing tables do not understand what a natural key is.” I have no way to validate the 90% claim, but, I have found through experience that the use of only system assigned keys is used in a preponderance of database schemas I have observed. Much of it is due, in my opinion, to the seductive nature of having a single unique identifier on every table, always of the same data type.
By having a sequential, system assigned, unique identifier in every table, it is easier to write ORM generating tools. Before we started seeing ORM generators, if an numeric field was used as a primary key for a table, it was usually named [TableName]Id. This naming convention left no ambiguity as to the authoritative source of the value. It was system assigned by the table of the same name. Before we had declarative referential integrity, we were able to determine foreign key relationships easily, because the foreign key column name, and the primary key column name were exactly the same. Moreover, you knew what table was being related because the table name was part of the Primary Key.
Today, with referential integrity in place in most relational data engines, the naming convention is no longer necessary to help define what is going on. So, I get a lot of pressure from developers with ORM tools to reduce the sequential, system assigned value to simply Id. The reason: it makes it easier to write the ORM code. Perhaps some things should not be easier. I have noticed that using code first modeling in EF, or hand written mapping in tools like Hibernate, tends to make it easier to use an ORM without simple naming conventions.
I reverse engineered the database schema of the first Microsoft release of SharePoint. Every table had a primary key Id. There was no declarative referential integrity. As a result, I had to manually build the relationships in my diagraming tool, because it could not determine the relationships. Of course, we humans can figure it out, and I’m sure more modern tools today can figure it out. So, I suppose this is more of a rant than a point of value.
The main topic I wanted to address is the point Kenneth makes about What Is a Natural Key. This is a great topic for a site such as SSWUG. Many people gain insight from our content, entering into the industry, or taking on different roles where they are responsible for a database design. Tomorrow I want to dig into this topic much more thoroughly.
Thanks, Kenneth, for the idea.
Cheers,
Ben