Entity-Relationship-Diagrams

This entry was posted on by

An Entity-Relationship-Diagram (ERD) is a logical model used in early stages of database development.  An Entity is an object that you want to record data about.  You can think of this like your customers, an order or an email campaign.  A relationship is a connection between two entities. Every order has a customer number associated with it.  Some contacts in your database have been sent an email.

ERDs are a visual mapping of every entity and relationship that let non-technical users understand the design of a database.

There are several ERD patterns, but the one I will describe is a basic version – but is general enough that it covers all the bases.

An Entity is represented as a box with its name in the center (Customers).  The entity can have attributes – e.g. Name, Birthdate, gender – and they are represented on the ERD with circles.

One Entity with Three Attributes

A single Entity with Attributes

An ERD does not describe what type of data is being stored.  You don’t know if Gender is stored as {M, F} or {1,0} or {Male, Female}.  ERDs create the logical model and the physical design of a database comes later.

When you have two or more entities in your ERD, they will be connected in some way – like Customers to Invoices.

The Relationship Part of an ERD

Relationships are the connections between two entities.  They are typically named with some sort of action verb (e.g. a customer IS BILLED an invoice).

Two Entities with a Relation Between Them

Two Entities with a Relation Between Them

There biggest change is the diamond shape between Customers and our second entity Invoices.  The diamond is the visual representation of a relationship.

  • The ERD says we are recording information about two entities – Customers and Invoices – and we know that customers are billed invoices.
  • Invoices has four attributes – InvoiceID, InvcDate, TaxesBilled and AmountBilled.
  • Notice InvoiceID and CustID are underlined – that means they’re the primary key for that entity.
  • The primary key means every InvoiceID will represent a unique invoice and every CustID will represent a unique customer.

There’s one more step in an ERD – defining cardinality between two entities.

 Cardinality – How Many Instances Will Exist in Each Table

Cardinality is the secret sauce of an ERD.  It defines where a primary key will exist as a foreign key and if a relationship needs to be converted into its own table.

There are only four types of relationship but each side of the relationship can be mandatory or optional.  Knowing if one side is mandatory or optional will guide you in where a foreign key should exist.

  • 1:1 (One-to-One): A student (one) is registered for  one dorm room bed and a dorm room bed can only have one student in it (on the books).
    • If one side is mandatory and the other optional – You’ll store the primary key on the mandatory side as a foreign key in the optional table.
    • Both sides optional – Either side can contain the foreign key.
    • Both side mandatory – You could combine these tables.
  • 1:M (One-to-Many): A customer (one) can have many invoices.  However, one invoice can have only one customer.
    • Many side is optional – Create a new table with the combined key from both sides.
    • Many side is mandataory – Store primary key of the one side in the many-side table.
  • M:1 (Many-to-One): Many citizens have one state district representative.  One representative represents many citizens.
    • See above!
  • M:N (Many-to-Many): Many students can be enrolled in many courses.  Many courses have many students.
    • In all cases, you create a new table with the primary keys of each side acting as a combined key for the new table.
Example of Cardinality in an ERD

Two Entity ERD with a One-to-Many (Optional) Relationship.

The final ERD above shows two entities and one relationship.  Here’s how you would describe this ERD:

  • CustID is the primary key for the Customers entity which has three other attributes.
  • InvoiceID is the primary key for the Invoices entity which has three other attributes.
  • An invoice MUST HAVE one and only one Customer associated with each invoice.
  • Customers MAY HAVE one or more Invoices associated with them.

Hat Tip

  • Database Design Using Entity-Relationship Diagrams (2nd ed., Bagui, Earp)
  • CSC-451 Database Design at Depaul University