Appendix A - Data Modelling Notation
Appendices
Many data modelling conventions are used today, but we will keep things simple for this book. The data models presented all comprise two basic components: Entities and Relationships. Yet, with these two simple concepts, we can capture highly complex structures that can be used to describe the business.
Entities
In a data model, an entity is shown as a box labelled with its name, as shown in Figure A.1.
Each entity represents some concept recognised within the business. Therefore, an entity in the data model represents a (Core) Business Concept.
Sub Typing
Entities can be organised by nesting them to represent taxonomies. Figure A.2 shows that Employees and Customers are types of Person. Similarly, Persons and Organisations are types of Party.
Entities contained within a parent entity inherit all the characteristics of the parent. The parent entity is a super-type, and the child entity is the sub-type. For example, a Customer is a sub-type of Person, whereas an Organisation is the super-type of Supplier. A sub-type entity is a more specific type of its parent super-type. An Employee is a specific type of Person.
This type of taxonomy also implies that sub-types are mutually exclusive. According to this model, a Person who is an Employee cannot also be a Customer. If, in the context of the business, that rule is not true then the model is incorrect and we would need to come up with an alternative.
Relationships
Unsurprisingly relationships, shown in Figure A.3 as the lines joining entities, indicate an association between two entities.
Usually, the relationship is named usually in a verb form allowing the relationship to the read in natural language. The relationships in the diagram may be read as follows:
A Purchase is made by a Customer.
A Purchase is made at a Store.
A Purchase is assisted by an Employee
A Purchase Item is part of a Purchase
A Purchase Item is for a Product
Cardinality
The symbols that decorate each end of the relationship indicate the cardinality of the relationship. It tells us how many of one instance of each entity is related to the other. There are four possible cardinalities:
Referring back to Figure A.3, we can say of the relationship between Customer and Purchase that:
A Customer can make zero one or more Purchases. You are allowed to be a Customer even if you have never made any Purchases.
A Purchase is made by exactly one Customer. A Purchase cannot be made unless there is a Customer involved. Every Purchase must be assigned to a single Customer, no more and no less (although a Customer may make many Purchases).
Also
A Purchase is made up of one or more Purchase Items. In other words, a Purchase cannot exist unless it has at least one Purchase item.
A Purchase may or may not be made at a Store. Online Purchases do not need to be associated with a Store.
Many-to-Many Relationships
All the relationships so far we can term as one-to-many relationships, meaning that at one end of the relationship, we have a Zero-or-One or a One-and-only-One cardinality and at the other end a Zero_One-or-Many or a One-or-Many cardinality. However, it is possible to have a many-to-many relationship where many of one entity may be related to many of another and vice-versa.
Figure A.4 shows a relationship between Product and Customer, in this case, the bought by. Reading the relationship in each direction, we can say:
A Product is bought by zero, one or more Customers.
A Customer may buy zero one or more Products.
This makes perfect sense, and at a conceptual level, this is a perfectly adequate description, but the many-to-many relationship might be hiding some detail. It might be that there is some detail we haven’t considered, and there are some other entities we need to add to the model.
We have already seen this detail in Figure A.3. To join the Customer and Product entities, we have to navigate through the Purchase and Purchase Item entities. Once these entities have been revealed, the many-to-many relationship disappears. In data-modelling speak, we say that the many-to-many relationship has been resolved. It is always worth exploring what might be lurking behind a many-to-many relationship; it could be something important.
Many thanks to the kind folk at Ellie for granting me access to their cloud-based business glossary and data modelling tool, which I’ve used to produce the diagrams in this article.