I’m currently working on a modeling exercised for connected applications. Modeling an RDBMS schema for a standalone monolithic appliaction is not a real challenge. On top of a set of connected applications, this task start to raise some concept that requires some guidance.
The article Data on the outside vs. the inside from Pat Helland highlight some of this concept.
As I mentionned in this post about persistance, it’s very common to see RDBMS primary keys appearing in entity definition. This is a mistake for a couple of reasons. First, entities should be loosely coupled from the persistance type (RDMS, file, memoty…etc). Using a primary key is introducing technical details from the implementation in the model. The second reason I see is about the immutability of the key.
When i’m talking about key, it’s about the part of the reference of a business entity that identify it. For example, in a REST environement you could reference purchase orders using this url : customer/C00123/PO/PO00112. In this case the key is the PO number (PO00112).
The goal of a model (or schema) is to support business application in describing things. 2 things that may seam different are in fact the same from the business perspective. For example, from an asset management perspective, if a laptop already acquired is replaced by another one (of almost the same model) because of a technical problem/failure, the second laptop is concidered as the same. In this particular case, nothing change in the description of the laptop even if physically there are 2 distinc laptops.
So to define the key, you need to answer to this question: from the business perspective, what qualify/reference uniquely an entity type. In the case of laptop for AssetManagement it’s the AssetTag, in the case of employee in HR it will probably be the social number…etc.
Immutability of such key means that key of an entity can’t change. If you want to change it because you assigned a temporary one for example) that measn your application should expose a process for this and in fact you will create a new entity with the new key and look at all attributes and relationship to apply them on the new entity. And this process should trigger all audit system.