A short guide to designing Entity Relationship Models using Barker’s notation.
By Frankie Inguanez
Frankies take on ERD is more in-depth that the previous video tutorial, whilst also being slightly harder to understand unless you really break down each section. As I work through the document I aim to write down and record the various key elements as a way of both demonstrating that I have understood the material to myself whilst simultaneously making notes which I can look to in the future when designing my own ERD databases. This material also specifically uses Barkers notations which is a variation of the crows foot notation seen previously.
It was half way through taking notes that I came to the realisation that it was complete overkill and I was essentially just repeating what he had written down first. So I have chosen to use his sheet as my guidelines rather than recreate his work, I included the notes I did take before stopping in this blog post.
General Rules of ERDs
- Capture all required information.
- Information appears only once.
- Model no information that is derivable from other information already modelled.
- Information is in a predictable, logical place.
Entity – is again the top element, this again represents a table with an entity instance being a single record or “row” of the table.
Attribute – is again the property of an entity which describes a characteristic of particular entity instance. However while the previous work featured primary keys and identifiers as a separate element here they are listed as a type of attribute. These three types are
“a. Unique Identifier: A UID is an attribute whose value uniquely identifies an entity instance. A UID is implemented as a Primary Key.
- Mandatory Attribute: A mandatory attribute is one whose value cannot be null.
- Optional Attribute: An optional attribute is one whose value can be null.”
Relationships – Links two or more entities (tables) together. Here a relation is implemented as a foreign key, therefore the FK “attribute” is not listed as an attribute in the target entity.
Diagram Showing the entity and attribute drawing rules.
Diagram showing the relationship drawing rules.
When drawing the relationships there are a number of rules to follow which I have copied into the diagram with lines showing where they are being used. But to reiterate a relationship must be optional or mandatory and each relationship must be labelled with its perspective according to the entity it is attached to.
Relation ships can be one of three different types
One to one – Pretty obvious, one entity instance relates to one entity instance. The FK or foreign keys relating them should always be placed where A. there is never going to be a null value so in the “must have” entity. Or in the entity which has the least amount of nulls/or rows or the most relevant to the table.
One to many – Each entity is related to multiple entity instances. The foreign key should be placed in entity touched with the crow’s foot, since many rows in B will be related to only one row in A.
Many to many (Always need to be resolved using an intermediary or transactional table)