Levels of Data Modeling

The same library model has to be read by three different people.

The librarian wants to check that nothing is missing. The developer wants to know which columns exist and how tables link. The database administrator wants to know the exact types, the indexes, and how much disk it will need.

One drawing cannot serve all three. Show the librarian a page of VARCHAR(255) and she stops reading. Show the administrator a box labelled Member with no columns and it tells her nothing.

So a data model is drawn three times, at three levels of detail. They are called conceptual, logical, and physical, and every model passes through all three in that order.

Conceptual: what things exist

A conceptual data model shows the entities and the relationships between them. Nothing else.

No attributes. No keys. No data types. A box with a name, and a line to another box with a name.

This is the level closest to how the user sees the business. It is drawn in the language the librarian already uses, which is why it is the level you review with her.

For the library, the conceptual model says: a Member borrows a Copy, and a Copy is of a Book. Three boxes, two lines, and a conversation.

It is also independent of technology. The same conceptual model would hold if you stored the data in a relational database, a document database, or a paper register.

The question it answers is: have we found all the things, and are the links between them right?

Logical: what facts we keep about them

A logical data model adds the detail, while staying away from any particular database product.

It adds attributes: a Member has a name, an email, and a joining date. It adds keys: member_id identifies a member, and a Loan holds member_id and copy_id as foreign keys. It gives each attribute a general type, like text, number, or date, and not a product specific one.

This is the level where normalization happens. Normalization is the set of rules for removing repeated data from a design, and it is the subject of a later chapter in this course.

The logical model is business focused. It describes what the organisation needs to record, in a form a developer can work from, without committing to MySQL or PostgreSQL or Oracle.

The question it answers is: exactly which facts do we store, and how do the tables connect?

Physical: how it is actually stored

A physical data model is the logical model rewritten for one specific database management system.

Now the types get exact: VARCHAR(120) for a name, DATE for a joining date, BIGINT for an id. Now you add indexes, because a query that finds loans by member will be slow without one. Now you decide on partitioning, storage settings, and anything else the product offers.

The physical model is implementation focused. Move to a different DBMS and this level is rewritten, while the two levels above it stay as they are.

The question it answers is: how do we make this run well on the database we chose?

Here is the library's Loan table at the physical level.

CREATE TABLE Loan (
  loan_id    BIGINT       NOT NULL AUTO_INCREMENT,
  member_id  BIGINT       NOT NULL,
  copy_id    BIGINT       NOT NULL,
  issue_date DATE         NOT NULL,
  due_date   DATE         NOT NULL,
  PRIMARY KEY (loan_id),
  FOREIGN KEY (member_id) REFERENCES Member(member_id),
  FOREIGN KEY (copy_id)   REFERENCES Copy(copy_id),
  INDEX idx_loan_member (member_id)
);

Every line of that came from a decision made at one of the three levels. The table and its columns came from the logical model. The entity itself came from the conceptual model. Only the types, the index, and the AUTO_INCREMENT are new here.

The three levels compared

Why three levels instead of one

Each audience gets a document it can actually read. A librarian reviewing a conceptual model finds missing entities. That same person given a page of SQL finds nothing.

Mistakes get caught while they are cheap. A missing entity found at the conceptual level costs a conversation. The same mistake found after the tables hold two million rows costs a migration.

Work is separated from technology. The conceptual and logical models survive a change of database product. Only the physical model is rewritten.

This last point is data independence, which the lesson on database management systems defined. The three levels are how you get it during design, rather than only after the database is running.

Moving between the levels

In practice the levels are not one way. Working out the logical model often shows that the conceptual model was wrong, and you go back and change it.

That is normal and cheap, and it is the reason the conceptual model is kept simple. A drawing with three boxes is easy to throw away. A hundred lines of SQL is not.

Key Takeaways

• A data model is drawn three times, at three levels: conceptual, then logical, then physical.
• The conceptual model shows only entities and relationships. It is the level closest to how the user sees the business, and the level you review with business people.
• The logical model adds attributes, keys, and general data types, and it is where normalization happens. It is business focused and independent of any database product.
• The physical model rewrites the logical model for one specific DBMS, with exact data types, indexes, and storage choices. It is implementation focused.
• The difference that matters most: logical describes what the business needs, physical describes how one product will store it.
• Changing the database product rewrites only the physical model, which is data independence applied during design.
• The levels are not one way. Detail found at a later level often sends you back to correct an earlier one.

You now know what the three levels contain. The next lesson is about the order of the work itself: the steps you follow, from a first conversation to a running database.