ER diagrams: designing a database before you build it

Why ER diagrams?

An ER (Entity–Relationship) diagram is a picture of a database: which tables exist, what columns they have, and how they are related.

• To explain the design to non-technical people. Suppose a senior person in your company isn't technical and you need to explain how the database is organised. You can't open phpMyAdmin and say "look, these are my tables". An ER diagram shows what's going on behind the scenes in a way anyone can follow.
• As a blueprint. You design the ER diagram first, discuss it with your team and seniors, and only then create the database. Changing a drawing is cheap; changing a live database isn't.

The tool: Lucidchart

Many tools can draw ER diagrams, and some must be installed. Nitish uses Lucidchart because it's online and needs no setup:

1. Sign up (the free plan is enough; it limits how many documents you can have at once). Logging in with Google works.
2. Click New → Start with a template.
3. Search for "ER diagram" and pick the Database ER diagram (crow's foot) template.
4. Delete the sample content, rename the document (e.g. Swiggy database) and start drawing.

How to build one: the Swiggy example

Nitish designs the database behind a food delivery app like Swiggy. Think about how you order food and which "things" are involved.

Step 1 · Find the entities

An entity is a real-world thing you can make a table about. For Swiggy: the customer who places the order, the restaurant, the food items, and the order itself. You could add more (delivery partner, payments, reviews…), but four are enough to learn the idea.

Step 2 · Give each entity its attributes and types

Step 3 · Connect entities with relationships

For every pair of related entities, just ask: what's the cardinality between these two, 1:1, 1:N or M:N? Then draw the line with the right crow's foot ends.

Step 4 · Export to SQL and create the database

Once the diagram is done, Lucidchart can export it as SQL (choose MySQL). Nitish then:

1. created a new empty database in phpMyAdmin,
2. opened its SQL tab, pasted the exported code and clicked Go,
3. fixed a typo the export had picked up from the diagram (a misspelled DECIMAL), and ran it again.

All four tables appeared, empty but with the full structure and relationships. Making a database from a diagram is that easy. The exported SQL looks roughly like this (you'll learn every line in Chapter 7):

CREATE TABLE customers (
  cid   INT PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(10)
);

CREATE TABLE restaurants (
  rid     INT PRIMARY KEY AUTO_INCREMENT,
  name    VARCHAR(255),
  cuisine VARCHAR(255),
  rating  DECIMAL(2,1),
  address VARCHAR(255)
);

CREATE TABLE food (
  fid   INT PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(255),
  type  VARCHAR(10),
  price INT
);

CREATE TABLE orders (
  oid        INT PRIMARY KEY AUTO_INCREMENT,
  cid        INT,
  rid        INT,
  order_time DATETIME,
  FOREIGN KEY (cid) REFERENCES customers(cid),
  FOREIGN KEY (rid) REFERENCES restaurants(rid)
);

-- junction table for the M:N between orders and food
CREATE TABLE order_items (
  oid      INT,
  fid      INT,
  quantity INT,
  PRIMARY KEY (oid, fid),
  FOREIGN KEY (oid) REFERENCES orders(oid),
  FOREIGN KEY (fid) REFERENCES food(fid)
);

Practice idea

Nitish's suggestion: take apps you use every day (WhatsApp, Instagram, Uber…) and try drawing their ER diagram. When you study computer science you'll build such applications yourself, and thinking about how their database could look is excellent practice for schema design.

Conceptual questions

These check whether you understand why, not just what. Several go one step beyond the video.