How to Draw an ER Diagram (Step by Step)
An ER diagram is the blueprint of a database. Get it right and the tables almost write themselves. This guide walks through the parts of an ER diagram and builds one for a library management system — the most common DBMS project.
Databases · 2026-10-14 · 9 min read
The parts of an ER diagram
| Part | What it is | Library example |
|---|---|---|
| Entity | A thing you store data about — becomes a table | Book, Member, Loan |
| Attribute | A property of an entity — becomes a column | title, author, isbn |
| Primary key (PK) | The attribute that identifies each row uniquely | book_id |
| Foreign key (FK) | A column that points to another table's primary key | Loan.member_id |
| Relationship | How two entities are connected | A member borrows books |
| Cardinality | How many of one relate to how many of the other | One member → many loans |
Chen notation vs crow's foot
Textbooks often use Chen notation: rectangles for entities, ovals for attributes and diamonds for relationships. Industry tools and most project reports use crow's foot notation: each entity is a box listing its columns, and the line ends show cardinality — a single bar for "one", a three-pronged "crow's foot" for "many". Both describe the same design; check which one your syllabus expects.
Step 1 — List the entities
Read the problem statement and underline the nouns that need their own records. For a library: Book, Author, Member, Loan (a borrowing), and Fine. Words like "date" or "name" are attributes, not entities.
Step 2 — Add attributes and keys
- Book: book_id (PK), title, isbn, category, copies_available
- Author: author_id (PK), name
- Member: member_id (PK), name, email, phone, joined_on
- Loan: loan_id (PK), book_id (FK), member_id (FK), issue_date, due_date, return_date
- Fine: fine_id (PK), loan_id (FK), amount, paid
Tip: Give every table a simple surrogate key like book_id. Natural keys such as ISBN can change or repeat across editions.
Step 3 — Connect the relationships
- Member – Loan: one-to-many. A member can have many loans; each loan belongs to one member.
- Book – Loan: one-to-many. A book can be borrowed many times over the years.
- Loan – Fine: one-to-zero-or-one. A loan gets a fine only if it is returned late.
- Book – Author: many-to-many. A book can have several authors and an author writes several books. A database cannot store this directly, so add a junction table BookAuthor(book_id, author_id).
Example: Library management ER diagram with keys and relationships.
Step 4 — Check it with real questions
A good ER diagram can answer the questions the system will be asked. Can you list all books a member has not returned? (Loan where return_date is empty.) Can you find overdue loans? (due_date before today.) Can you show every book by an author? (through BookAuthor.) If a question needs data you are not storing, add the attribute or entity now.
Step 5 — Turn it into tables
Each entity becomes a table, each attribute a column, and each one-to-many relationship becomes a foreign key on the "many" side. Many-to-many relationships become their own table. AI Flowchart can export your ER diagram straight to SQL CREATE TABLE statements (MySQL, PostgreSQL, SQL Server, SQLite) or to Prisma and Mongoose models.
Common mistakes
- Storing lists in one column (authors = "A, B, C") instead of using a junction table.
- Putting the foreign key on the wrong side of a one-to-many relationship.
- Making an entity for something that is just an attribute (a "Date" entity).
- Forgetting cardinality on the lines — examiners always look for it.
Try: hospital management ER diagram →
Frequently asked questions
What are the main components of an ER diagram?
Entities (things you store), attributes (their properties), keys (primary and foreign), and relationships between entities with their cardinality (one-to-one, one-to-many, many-to-many).
How do you show a many-to-many relationship?
In the conceptual diagram, draw it as a relationship with "many" on both sides. In the database design, replace it with a junction table that holds the primary keys of both entities.
Can I convert an ER diagram into SQL?
Yes. In AI Flowchart, open your ER diagram and use Code to generate CREATE TABLE statements for MySQL, PostgreSQL, SQL Server or SQLite, or models for Prisma and Mongoose.