Conceptual data modeling explained: definition and four examples

What a conceptual data model is

A conceptual data model answers three questions in plain language:

• What do we keep records of? Customers, orders, products.
• How are they connected? A customer places orders. An order contains products.
• What rules apply? An order belongs to exactly one customer. A product can appear in many orders.

It is the first of three levels of data modeling. The logical model adds attributes and keys, and the physical model adds everything a specific database needs. In a DBMS course the conceptual model is the stage where an entity-relationship diagram is first drawn.

Conceptual data model examples

Four examples follow. Each is drawn with a few attributes so the entities are easy to recognise. A strict conceptual model would show only the entity names and the lines.

Retail sales

• Entities: Customer, Order, Order detail, Product.
• Relationships: a customer places many orders, an order has many detail lines, each line refers to one product.
• A rule worth writing down: is a returned order still an order? The answer decides every revenue report built later.

University

• Entities: Student, Enrollment, Course, Instructor, Department.
• Relationships: a student enrols in many courses, a course has many students, an instructor teaches courses and belongs to a department.
• Why Enrollment is an entity: the link between student and course has facts of its own, such as the term and the grade.

Healthcare

• Entities: Patient, Doctor, Department, Appointment, Treatment.
• Relationships: a patient books appointments with doctors, a doctor belongs to a department, an appointment can lead to treatments.

Hotel reservations

• Entities: Guest, Room, Reservation, Payment.
• Relationships: a guest makes reservations, a reservation is for a room and has payments.
• A rule worth writing down: can one reservation cover several rooms? The diagram has to say, because the answer changes what one row of Reservation means.

Components of a conceptual data model

A conceptual model has five kinds of statement. The last two are the ones most often skipped.

Entities

An entity is something the business would name in a meeting and needs to keep records of: Customer, Order, Product. A good test is whether it has more than one instance and whether those instances need telling apart.

Attributes

Attributes are the facts recorded about an entity. At the conceptual level, list only the ones that define it or that people argue about: an order’s status, a customer’s segment. The full list belongs in the logical model.

Relationships

A relationship is a named connection between two entities: places, contains, belongs to. Name it with a verb. A line without a verb hides the question of what the connection means.

Cardinality

Cardinality says how many of one entity go with how many of the other: one to one, one to many, many to many. “One customer, many orders” is a statement about the business, and getting it wrong later duplicates rows in every join.

Business rules

Rules are the conditions the data must meet: an order must have a customer, a room cannot be reserved twice for the same night. A model that carries these rules through to how each number is calculated is a semantic data model. They cost nothing to write here and are expensive to discover in a report.

Conceptual vs logical vs physical data models

The three levels describe the same data with increasing detail.

The same sales process at each level:

A longer comparison is in logical vs physical data models, and the wider classification is in types of data models.

How to create a conceptual data model

Six steps, in this order. The whole exercise is a few hours for one business process, not a project.

1. Pick one process and one question. “How we sell” and “what was revenue by product last quarter” give a boundary. A model of the whole company at once never finishes.
2. List the nouns. Walk through the process with the people who run it and write down every thing they name. Those are candidate entities.
3. Merge and split. Two names for one thing become one entity. One name used for two things, such as “customer” for both the buyer and the account, becomes two.
4. Draw the relationships and name them. One verb per line, with cardinality on both ends.
5. Write the rules that change a number. Which statuses count as a sale. When a customer counts as new. Keep the list short and specific.
6. Check it against real data. Take ten real orders and see whether each fits the diagram. The exceptions are the model’s missing parts.

A whiteboard or any diagram tool is enough to draw it. A comparison of free options is in database diagram design tools.

Common mistakes

Four problems account for most conceptual models that get ignored.

• Starting from the tables. Copying the source system’s schema produces a physical model with the detail removed. Start from the process and the questions instead.
• No business owner in the room. Analysts can draw the boxes alone, but they cannot decide alone what counts as a customer.
• Unnamed relationships. A line between Customer and Order that nobody labelled will be read three ways.
• Stopping at the diagram. A model that lives in a slide deck is out of date within a month, and nothing tells its readers.

More in mistakes in data modeling.

Keeping the conceptual model true after it ships

The last mistake is the expensive one. A conceptual model records decisions: completed orders only, one customer per order. Those decisions end up in SQL, and the diagram and the SQL then change separately. When a report disagrees with the diagram, nobody can say which one is right.

One way to prevent that is to keep the decisions where the queries are. In OWOX Data Marts, each entity becomes a Data Mart: SQL, a table or a view that an analyst defines, with a description, an owner and described fields. Each line in the diagram becomes a relationship between two Data Marts, declared on their join keys. Reports in Google Sheets or Looker Studio read those Data Marts, so the rule written in the Orders Data Mart is the rule in every report.

The analyst still does the modeling and writes the SQL. What changes is that the model people agreed on is the thing the reports run on, and its definitions can be read by whoever questions a number. The industry data models show finished examples for several kinds of business, and Joinable Data Marts covers how relationships are declared.

Data ModelingWhat is data modeling? Process, types and techniquesFebruary 27, 2025→Data ModelingTypes of data models: the three levels and seven structures, with examplesOctober 7, 2026→Data Modeling8 data modeling mistakes and how to avoid themApril 9, 2025→

See all articles →