Skip to content
BENIBIX Learn

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

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

LevelShowsWho reads it
ConceptualThe main things and how they linkBusiness people
LogicalFields and keys, for no one productAnalysts and engineers
PhysicalReal tables, types and indexesThe database and its engineers
The same plan, drawn three times. Each level adds detail, and each one is read by different people.

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.