What Data Modeling Is & Why It Matters

A data model is the plan for your tables. It says which tables you have, what one row means, and how the tables connect.

You make the plan before you put any data in the tables. A good plan gives right numbers. A bad plan gives wrong numbers, even when every row is correct.

This chapter uses the same shop as the SQL chapters. You already know these three tables. Look at them as a plan, not just as data.

You will learn four things in this chapter.

• What a data model is.
• Why a wrong model gives wrong numbers.
• The three levels of a model.
• The questions to ask before you draw anything.

A data model describes the things a business cares about, and how they connect. Each such thing is called an entity. In the shop, the entities are customer, order and order item.

An attribute is one fact about an entity. A customer has a name and a country. An order has a status and an amount.

A relationship says how two entities connect. One customer places many orders. Each order belongs to one customer.

In a database, an entity becomes a table and an attribute becomes a column.

Two column labels appear in the diagram. PK marks the primary key, the column that identifies each row. FK marks a foreign key, a column that points to a row in another table.

The diagram below shows the shop as a model. Each box is a table.

Look at order 101 in the orders table at the top of this chapter. Its customer_id is 1. That is the customer_id of Asha in customers.

The model is the shape. The rows are the data that fill the shape. A good shape helps correct rows give correct answers.

A model also leaves things out on purpose. The shop does not store the color of a bag, because nobody asks about it. Keep only what the business needs.

A good model still gives wrong numbers if the query mixes grain. A bad model gives wrong numbers even with a good query. Grain is what one row stands for.

A total that is too big

In the SQL joins chapter, a join repeated an order once for each of its items. That is called fan-out. Here you see what it does to a total.

The question is simple: what is the total amount of all orders? First, the sum after a join to order_items.

select sum(o.amount) as total_amount
from orders o
join order_items i on i.order_id = o.order_id;

The true total is smaller. Each order amount must count once. Run the sum on orders alone.

select sum(amount) as total_amount
from orders;

The join gave 340.50. The right answer is 220.50. The extra 120.00 is the amount of order 101 (40.00) and order 106 (80.00), counted a second time.

The cause is two different grains in one query. In orders, one row is one order.

In the joined rows, one row is one item. The amount belongs to the order, not to the item.

The same fact in two places

Another problem comes from the model itself. Here is a single table that holds the customer country on every order.

Asha moves to GB. Someone changes order 102 and forgets order 101. Now the table says Asha lives in two countries.

In the real model, you change the Asha row in customers once. Only one row says where Asha lives.

You can describe a model with more or less detail. There are three standard levels: conceptual, logical and physical. They describe the same shop, and the customer stays a customer at each level.

Conceptual: the sentences

The conceptual level uses plain sentences, with no columns. Anyone in the business can read it and say yes or no.

• A customer places orders.
• An order contains items.

Logical: columns and keys

The logical level adds the columns, the keys and the rules. It does not name a database. Here is the shop at this level.

Look at the keys. The conceptual level has none. Only now can an engineer see that orders.customer_id points to customers.

Physical: real tables

The physical level writes the tables for one database. It adds data types and rules the database checks for you.

Three lines in the code that follows matter most. Read them first.

• "references customers" is the foreign key.
• "primary key (order_id, product)" says one order has one row per product.
• "check (quantity > 0)" stops a quantity of zero or less.

create table customers (
  customer_id integer primary key,
  name        text not null,
  country     text
);

create table orders (
  order_id    integer primary key,
  customer_id integer not null references customers (customer_id),
  status      text not null,
  amount      numeric(10, 2)
);

create table order_items (
  order_id    integer not null references orders (order_id),
  product     text not null,
  quantity    integer not null check (quantity > 0),
  primary key (order_id, product)
);

numeric(10, 2) means a number with up to 10 digits, and 2 of them after the decimal point. It fits money such as 15.50.

Work in this order: conceptual, then logical, then physical. People who start with CREATE TABLE often argue about data types before they agree what a customer is.

Do not start with tables. Start with questions. Imagine your manager says: "Show me sales by customer country."

Ask these five questions. They give you the entities, the grain and the keys before you draw one box.

1. What does the business want to ask? Which numbers, and split by what?
2. What things exist? These become entities.
3. What is one row in each table? This is the grain.
4. What identifies one row? This is the key.
5. How do the things connect?

Now answer them for the manager request, using the shop.

The answers show the plan. The amount lives in orders, and the country lives in customers. You do not need order_items at all.

We count paid orders only. Ask the manager if that is right. Then join the two tables.

select c.country, sum(o.amount) as total_amount
from orders o
join customers c on c.customer_id = o.customer_id
where o.status = 'paid'
group by c.country
order by total_amount desc;

Dev has no country, so order 106 has a NULL group. Ask the business whether that group should be called unknown.

Interviewers often say: "Design the tables for an online shop." Many candidates draw a star schema at once. A star schema is a report layout with one central table. It is the wrong first move.

Use the five questions from the last section. Say each answer. Last, ask: is this for running the shop, or for reports?

This chapter gave you the frame. The next chapters teach the parts one by one. You do not need them yet.

Say the grain of a table before you use it. If you cannot say it, the numbers will probably be wrong.

Each exercise opens a design canvas. Draw the tables, then run the review. The review checks your model against real rows.

The first one is already finished. Start there to see what a correct model looks like.