Designing an Entity Relationship Diagram (ERD)

As a data analyst, my job requires interacting with databases, often querying data and building new tables. While I’m not a database administrator or know much about managing them, I find it helpful to be able to both create and understand entity-relationship diagrams.

Working for startups I’ve been responsible for documenting existing databases and writing requirements for building new ones. And it’s a lot easier to have an informed discussion about designs when you can give your data engineer a diagram that illustrates what you’re looking for. Welcome the ERD, a visual representation of your database. Why didn’t I learn about this while at university!?

Lucidchart makes this process easy and you can get started for free. If you’re only looking to make a few diagrams and not using over 25mb of storage, the free account works great.

Crows Feet Notation
Crows foot notation. Source: Lucidchart

ERD cardinality

According to Wikipedia, cardinality, as it relates to data modeling, is the numerical relationship between rows of one table and rows in the other. Common cardinalities include one-to-one, one-to-many, and many-to-many.

Here’s the cardinality between two tables, customer and orders using Crows foot notation.

  • ring and dashminimum zero, maximum one (optional)
  • dash and dashminimum one, maximum one (mandatory)
  • ring and crow’s footminimum zero, maximum many (optional)
  • dash and crow’s footminimum one, maximum many (mandatory)
cardinality

In this example, a customer can have zero, one, or many orders whereas an order can only have one, and only one customer. This is the table-to-table cardinality, but we would connect columns between tables showing their relationship.

Here are the same two tables with some columns added.

Database table cardinality

The Customers table has the columns ‘id’ and ‘customer_name’. The Orders table has a primary key of ‘id’ and a foreign key named ‘customer_id’. Here we’ve connected the Customers.id primary key column with the Orders.customer_id column. And the same relationship applies as before, a customer can have zero or many entries in the Orders tables, where each order row will have one, and only one customer id.

Here are some additional examples.

One-to-one

A person can only have one birth certificate and a birth certificate can only have one person.

One-to-one (optional on one side)

A person can have no driver’s license, or they can have one. (Ignoring different types of licenses such as motorcycle, commercial, etc.) A driver’s license can only have one person.

Many-to-one

A person is only born in a single birthplace. A location can have one or many people born there.

Many-to-many

A person can read zero books or many. And a book can be read by zero people or by many.

One-to-many

A customer can have zero orders or many. A single order can have one and only one customer.

Many-to-many

A course can have one or many students. A student can take one or many courses.

Designing your database

Not that we understand how to illustrate relationships between our entities. We need some entities!

Lucidchart Entity Relationship shapes
Lucidchart shapes

Lucidchart provides a number of entity-relationship shapes for building diagrams. There’s a single column, (2) two columns, and a three-column entity shape. The three-column shape can be used to identify your key, column name, and data type. This is the entity that I use most often.

If you already have a database, then you can import its schema directly into Lucidchart (paid feature).

Let’s take a look at a more complete entity.

Here we have a ‘customers’ table and a ‘placed_orders’ table. The ‘customers’ table has an id column that is used as the primary key. In the ‘placed_orders’ table, the customer_id column is the foreign key that creates the relationship back to the ‘customers’ table. A customer can have zero or many placed orders, and an order can have one and only one customer.

From here it’s a matter of building it out. Identify all of the data you would like to, or are, collecting, entities for those data, and how everything relates to each other. Here’s where I usually start collaborating with a database expert, one that can make sure we build something efficient.

Export your schema

Lucidchart SQL export dialog

Lucidchart has a feature that allows you to export your ERD as SQL commands to build your tables. You can choose from four database management systems, MySQL, PostgreSQL, SQL Server, and Oracle.

There’s a lot of work upfront designing your entity-relationship diagram, but at least when it comes time to create your tables, it can be done very quickly.