Data Modelling

Goal

The decision the model supports is the structure of the persistent data. The BABOK states it as a strength of the technique: reviewing a logical data model makes it possible to check that the design of the persistent data correctly reflects the business need. A structural choice becomes expensive to undo once implemented, and the model is what makes it debatable beforehand.

The model answers three questions in that order: which things the organisation keeps track of, what it holds about each of them and how those things hold together. The answers are called the entity, the attribute and the relationship, and a relationship counts as settled only once it has been counted: each customer belongs to exactly one sales territory, a sales territory holds zero, one or several customers.

Usage

When to use it

• Designing a relational database: the persistent structure is settled before implementation.
• Designing object-oriented software: the classes and their associations frame the code to be written.
• Reviewing a data design: checking that the logical model answers the business need.
• Existing system with no documentation: rebuilding the real structure before any decision on reuse or migration.
• Domain suspected of being inconsistent: formal modelling brings out contradictions and requirements that prose was hiding.

When not to use it

• Strictly business audience, with no IT background: the formalism puts them off, hold the concept model or the glossary for that audience.
• Need limited to element definitions: format, length and allowed values belong to the data dictionary (10.12) alone.

The elements of the model

The BABOK breaks the model into five elements: the entity or class, the attribute, the relationship or association, the diagram that carries them and the metadata, which the model holds optionally.

Entity, attribute, relationship

An entity is whatever the business keeps track of: something physical (a warehouse), organisational (a sales territory), abstract (a product line) or an event (an appointment). Each occurrence carries a unique identifier. On a class diagram the same thing is called a class and carries one compartment more, that of the operations: issuing an invoice, opening an account.

An attribute is a piece of information held about the entity, together with the data type it carries. Its unique name, its aliases, its allowed values and its definition in the context of the solution are the content of a data dictionary entry. The allowed values are often set by a business rule held elsewhere, which business rules analysis covers.

A relationship says how two entities hold together. The BABOK gives the reading formula, which can be spoken as it stands in front of a domain expert: each occurrence of this entity is linked to a minimum and a maximum number of occurrences of that other entity. A relationship reads in both directions and the two readings are two distinct business rules, to be validated separately. On a class model the words change for the same idea: association and multiplicity.

Cardinality and optionality

Behind the word cardinality stand two independent questions, and treating them as one is the most frequent error on a logical model. The first is about the maximum: can one occurrence touch several on the other side or only one? The second is about the minimum: must it touch at least one, or may it touch none? The BABOK files both bounds under the single term cardinality. The tradition that comes from Barker's method names the second one optionality: a relationship is mandatory when its minimum is one, optional when it is zero.

Both bounds are set at both ends, which makes four values to validate for a single line of the diagram. On the relationship between Insured Person and Policy: an insured person holds zero, one or several policies; a policy is held by exactly one insured person. The minimum is rarely discussed and yet carries an implementation decision: writing zero lets the database hold an insured person with no policy at all, writing one forbids that and obliges the application to create the insured person and their first policy in the same transaction.

Answering the minimum with the business rule gives a false bound. Basic health insurance is compulsory for everyone resident in Switzerland, which invites writing one on the policy side. But the file of an insured person exists in the system between the application for membership and the issue of the contract, and it still exists after the last policy has been terminated. The minimum bound describes the states the database has to accept at every moment, including the transient states the business rule ignores.

A bound set by default is an invented business rule. The "many" put in as a matter of course because it costs less to undo grants a permission nobody gave, and it comes to light the day the application accepts a case the business refuses. Each bound is validated with the domain expert, in the terms of their own work: how many policies can one insured person hold at the same time, can one policy cover several people. In Swiss health insurance the second answer is no, each person being insured in their own right, including when the household receives a single invoice. A model that answers yes flattens the distinction between the contract and its billing.

Identifier, primary key, foreign key

The identifier appears as early as the conceptual level, where it names the attribute that tells one occurrence apart from the others. At the logical level it becomes the primary key and takes on three obligations: to be unique, to be filled in under all circumstances and to stay stable over time.

The natural key is an attribute the business already owns: the AVS number of an insured person, the number of a policy. The technical key, also called a surrogate key, is a meaningless number the system assigns for identification alone. A natural key reads, can be checked by eye on an extract of a table and saves a column. Two weaknesses often weigh against it. It changes the day the business changes the way it numbers, as the AVS number did in 2008 when it went from eleven to thirteen digits, and the change propagates into every entity that copies it. Any table that copies a person identifier also becomes a table of personal data, and the scope to be protected widens accordingly.

The foreign key is the attribute through which one entity carries the identifier of another; it holds the relationship at the logical level and its place is dictated by the maximum of the cardinality: in a one-to-many relationship it sits on the "many" side, the policy carrying the AVS number of its insured person. A many-to-many relationship offers no side to put it on. That is the sign that an entity is missing from the model. It is named, given its own identifier and its own attributes. The two relationships that leave it become one-to-many again: between Policy and Benefit, the intermediate entity is the Coverage, which carries the effective date and the co-payment rate.

The metadata

The fifth element, optional, describes the model itself: the origin and the use of the entities as well as the security, confidentiality and audit constraints that bear on one entity or on a whole group. On a domain where the entities carry health data, the confidentiality constraint held as metadata governs the access rights and the retention periods, and it is gathered from the business in the same way as a cardinality.

The three levels

Two models of the same domain may differ appreciably from one level to the next, because the three levels are developed for different purposes. This is the distinction practitioners confuse most often, and it is the one that decides who holds the pencil.

The conceptual data model is independent of any solution and of any technology. It shows the way the business perceives its information and it serves to establish a consistent vocabulary for describing it. It holds the entities of the domain and the relationships that matter, named in the words of the business. Those entities already aim at an implementable structure, and that is what separates this level from the concept model. The BABOK notes that at this level the notations produce broadly similar results, to the point where they can be treated as a single technique.

The logical data model is an abstraction of the previous one that incorporates the rules of normalisation, in order to formally manage the integrity of the data and of the relationships. It remains platform-independent, and it is associated with the design of a solution: identifiers, foreign keys, cardinalities set. Type, length and index wait for the next level, because they presuppose a database management system.

The physical data model describes the physical organisation of a database on a given database management system: tables, columns, types, lengths, indexes. Its concerns are performance, concurrency and security, and they can only be answered once the technology is chosen.

The conceptual level belongs to the business analyst: it presupposes no technology and the domain experts can confirm or refute it. The logical level is shared: the business analyst reads it, questions it and validates it against the need, the architect or the solution designer builds it. The physical level belongs to the database administrator and the engineer. The BABOK puts it in its own terms: logical and physical models contain elements specific to the solution they support and are generally developed by stakeholders competent in the implementation of particular technical solutions.

Normalising: the first three normal forms

To normalise is to remove from the model the redundancies that let the database contradict itself. If the address of the insured person is copied onto each of their policies, two policies of the same person may carry two different addresses, and nothing in the structure says which one holds. The operation files each attribute next to the identifier it depends on, and a foreign key carries the relationship between the entities that result.

The rules that govern the operation are the normal forms, stated by Edgar F. Codd: the first in the 1970 paper that founds the relational model, the second and the third in the 1971 research report that takes normalisation further. Each one adds a requirement to the previous one, and the first three cover what a business analyst needs to check on the logical model of a business application.

The first normal form requires that each attribute carry a single value and that no group of attributes repeat. A Claim entity carrying Benefit 1, Benefit 2 and Benefit 3 breaks it. The same information filed in a single column of comma-separated codes breaks it just as much. The remedy does not vary: what repeats becomes an entity, linked to the first.

The second normal form adds that each non-identifying attribute depend on the whole of the identifier, and it only bites on entities with a composite identifier. A Coverage entity identified by the pair policy number and benefit code, carrying the label of the benefit, breaks it: the label depends on the code alone and is copied onto every policy that includes that benefit. The label belongs to the Benefit entity.

The third normal form adds that no non-identifying attribute depend on another non-identifying attribute. A Policy entity carrying both the insurer code and the insurer name breaks it: the name follows from the code, and two policies of the same insurer may carry two different names as soon as one of them is keyed in by hand. The name belongs to an Insurer entity identified by its code.

A model in third normal form writes each fact in one place only, holds its relationships by keys and makes each attribute depend on the identifier of its entity and on nothing else. The forms beyond it, starting with Boyce-Codd normal form, deal with cases of several candidate keys: an entity in third normal form that carries only one candidate key already satisfies Boyce-Codd, which is the ordinary case in a business application.

Denormalising knowingly

The physical level sometimes goes back on normalisation. A normalised model spreads the information across many tables, and any read that gathers it again pays for that many joins. On a read-dominated workload, a dashboard, a data warehouse, a regulatory report, the physical design copies a value to avoid the join, and the star schema of a warehouse is the organised form of that copying. The price is paid in consistency: each copy has to be kept up to date and the design names the mechanism that does it, failing which the database recovers the contradiction normalisation had taken away from it.

A value frozen at a date looks like a redundancy and gets removed by mistake. The amount of the deductible applied to a claim, written on the claim, looks like a copy of the deductible carried by the policy. They are two facts: the policy carries the deductible in force today, the claim carries the one that applied on its date. Removing the attribute from the claim makes history depend on current values, and the first change of deductible rewrites the past.

Data model and concept model

A concept model (BABOK 10.11) captures meaning and vocabulary: the terms the business uses, the facts that connect them and the definitions the organisation agrees on. A data model captures the structure intended for storage and implementation: the entities, their attributes, the keys and the cardinalities. Ronald G. Ross states the boundary in one line: concept models are centred on the concept, data models on the thing, the entity or the class. He adds that a data model is derived from a concept model fairly readily, whereas the reverse path is markedly harder, which is the practical argument for starting with the vocabulary when the terms of the domain are disputed.

Three neighbouring objects carry almost identical names. The concept model is technique 10.11, the one about vocabulary. The conceptual data model is the most abstract level, and it remains a data model: it has entities and relationships, and it ultimately aims at an implementable structure. Merise's modèle conceptuel de données, the MCD, denotes for a reader in French-speaking Switzerland that same level, in the vocabulary of another school of modelling. The concept model establishes what the word "claim" means and that the policy covers a claim. The conceptual data model establishes that Claim is an entity linked to the entity Policy, and it opens the question of how many claims a policy may cover.

Choosing the right notation

The diagram of a data model is called an entity-relationship diagram (ERD), that of a class model a class diagram. The BABOK settles it in one sentence: logical and physical ERDs are used to implement a relational database, a logical or physical class diagram to support object-oriented development.

The criterion that settles the choice is the artefact being designed. The persistent schema is designed as an ERD, the structure of the code is designed as classes and an object-oriented application sitting on a relational database, the ordinary case in a Swiss company, produces both models: the ERD for its database, the class diagram for its code. The platform of the project leaves the question open, the artefact closes it.

Two secondary criteria refine the choice. Behaviour first: the class carries operations on top of its attributes and it is the only one of the two to carry them, so a domain where behaviour counts as much as structure is modelled as classes. The downstream reader next, when the model travels between several hands: the database administrator reads an ERD fluently, the developer a class diagram and a reviewer who comes from the business but is familiar with information systems, a domain owner or a management analyst, follows an ERD more readily, since its vocabulary remains that of entities and relationships.

The same relationship in two notations

The entity-relationship diagram and the UML class diagram write the same information in two ways. Cardinality is most often written in crow's foot notation, due to Everest (1976) and then popularised by Barker's method and the Oracle workshops, the one BABOK's figure uses and the one actually drawn on a whiteboard.

Crow's foot puts the count into a two-part symbol, one part for the maximum and the other for the minimum, where UML writes it out at the end of the association as an interval. Both notations place the mark on the side of the entity being counted, so a relationship reads the same way in either one, by following the line towards that entity.

On the relationship between Insured Person and Policy, the ERD carries a circle then a fork on the policy side and two bars on the insured person side; the class diagram writes 0..* on the policy side and 1 on the insured person side. The fact drawn is the same, the vocabulary changes: the ERD speaks of entity, relationship and cardinality, the class diagram of class, association and multiplicity.

Without an explicit transformation from one to the other, the consistency of the two models is checked by hand: a cardinality corrected on the ERD does not propagate to the class diagram.

A third school is found in French-speaking organisations. Merise writes the same domain as a conceptual data model, the MCD, then transforms it into tables through fixed rules. Its cardinality is written at the end opposite the one where crow's foot and UML place theirs: a model transposed from one school to the other without noticing asserts the inverse of the domain.

The link with the data dictionary

The data dictionary (BABOK 10.12) defines the elements: the unique name of an attribute, its aliases, its allowed values, its format and its length, its definition in the context of the solution. The data model gives the structure: which entities exist, which attributes belong to them, how they connect. The two artefacts hold together through the attributes, and the BABOK notes that the dictionary is often maintained jointly with an entity-relationship diagram, from which it can be extracted. A model without a dictionary shows boxes nobody knows the accepted contents of, a dictionary without a model gives fields nobody knows how to assemble.

AI considerations

A language model renders two services on this technique. The first is the derivation of a candidate model: it reads the schema of an existing database, columns, types, constraints and foreign keys, or a corpus of requirements documents and interview notes, and it proposes entities, attributes and relationships from them. On a legacy system with no documentation, the draft obtained is corrected faster than a blank page is filled. The second is the checking of normalisation: flagging a repeating group, a partial dependency or a transitive dependency is regulated work that lends itself well to the machine. The limit is of a business nature. What an entity means to the organisation and the fact that a relationship genuinely exists in its activity are facts the business holds and the model must reflect. A machine reading a schema infers what the database contains, including the structural errors it has accumulated; it cannot confirm that a policy covers a single insured person, because the answer lies in the way the organisation insures. Every AI proposal is a draft submitted to the domain experts.

Cost

Tools

Tools divide on what they keep, a drawing or a model. The general-purpose diagramming tool gives a clean diagram and checks nothing, so two entities can carry the same attribute under two names with nothing to flag it. It is enough at the conceptual level, which the business analyst holds with the business and corrects in the session. The data modelling workbench keeps a repository where the three levels are three views of the same model: it generates the creation script of the database, reverse-engineers the model from an existing database and compares it with the deployed schema, which brings out the gap between the two. The data catalogue takes over on the metadata: the origin of an entity, the classification of personal or health data, the retention period. The physical level sometimes escapes all three families, where the database is held by migration scripts versioned alongside the code: the sequence of scripts is what holds and the model is accurate only on the day it is reverse-engineered.

Sources

• IIBA, A Guide to the Business Analysis Body of Knowledge (BABOK Guide) v3, §10.15 Data Modelling.
• OMG, Unified Modeling Language (UML) Specification: the specification that defines the class diagram and its multiplicity notation.
• Peter Chen, "The Entity-Relationship Model: Toward a Unified View of Data", ACM Transactions on Database Systems, vol. 1, no. 1 (1976), pp. 9-36: the founding paper of the entity-relationship model.
• Gordon C. Everest, "Basic Data Structure Models Explained with a Common Example", Proc. Fifth Texas Conference on Computing Systems (1976): the origin of crow's foot cardinality notation.
• Richard Barker, CASE*Method: Entity Relationship Modelling, Addison-Wesley (1990), ISBN 978-0201416961: the Barker/Oracle variant of crow's foot and the tradition that names optionality as a question distinct from the maximum of the cardinality.
• E. F. Codd, "A Relational Model of Data for Large Shared Data Banks", Communications of the ACM, vol. 13, no. 6 (1970), pp. 377-387: the relational model and the first normal form.
• E. F. Codd, "Further Normalization of the Data Base Relational Model", IBM Research Report RJ909 (1971), reprinted in Data Base Systems, ed. Randall Rustin, Prentice-Hall (1972): the second and third normal forms.
• DAMA International, DAMA-DMBOK: Data Management Body of Knowledge: the reference of data management, which uses the same three levels, conceptual, logical and physical.
• Ronald G. Ross, "What Is a Concept Model?", Business Rules Journal, vol. 15, no. 10 (2014): the reference statement of the boundary between concept model and data model.