Data Models and ER Diagrams
Level 4 · Models and workloads
What a data model is and how an ER diagram draws one. Then the three levels of a model, from a sketch for the business to the real tables.
Course contentsLesson 9 of 11
Level 3 · Kinds of data
Level 4 · Models and workloads
After this lesson you can
- Read the boxes and lines of an ER diagram
- Tell conceptual, logical and physical models apart
- Say who each level of model is for
12 mincovers 5 glossary entries
Worth reading first: The Three Types of Data
What a data model is, and how its three levels fit
A data model is a plan of what data a system holds and how the pieces connect. It names the things, such as customers and orders, and the links between them. An entity relationship diagram, first described in 1976, draws that plan as boxes and lines. Models come in three levels of detail.
In an ER diagram, each box is an entity, a kind of thing worth storing. Each line is a relationship. The ends of a line show how many. A single bar means one, and a crow's foot, three short lines, means many. One customer places many orders, and each order belongs to exactly one customer.
A conceptual model is the simplest level. It shows the main things and how they relate, with no columns at all. Customer places Order. Order holds Product. It fits on one page, and it is for talking with the business, who can check it without knowing any SQL.
A logical model adds the detail. Each entity gets its fields, such as name and email, and its keys, the fields that point from one table to another. It still names no product. The same logical model could be built in PostgreSQL, in a warehouse or even in a sheet.
A physical model is the real thing, written for one database. It gives every column an exact type, such as a date or a whole number. It adds indexes for speed and splits big tables into parts. It is what the engineer turns into the code that builds the tables.
Each level catches a different mistake. The first one catches a rule nobody told you about, such as an order with two customers. The second catches a missing field. The third catches a slow query before it ships. Jumping straight to tables skips the first two checks.
People often think a data model is only the tables. The tables are just the last level. A team that never drew the first two ends up with tables that fit today's app. Then those tables fight every report the firm asks for next year.
The three levels of a data model
| Level | Shows | Who reads it |
|---|---|---|
| Conceptual | The main things and how they link | Business people |
| Logical | Fields and keys, for no one product | Analysts and engineers |
| Physical | Real tables, types and indexes | The database and its engineers |
Worked example
A school plans its sign-up system
Lucy draws three boxes for a school: Student, Class and Teacher. The head teacher looks at it and says a class can have two teachers, which her plan did not allow.
She fixes the line before any table exists. The change costs 10 minutes on paper instead of a week of rework later.
The words for this lesson5
Check yourself
Which level of a data model sets the type of each column?
Finished this lesson?
Marking it complete also marks its 5 glossary entries learned, which updates your roadmap and your saved page. 0 are already marked.