Entities, attributes and grain

01The problem

Kolanut wants to store its customers properly. Someone suggests one column called details holding "Peace Provisions, Kiosk, Kano, rep 7". It's easy to type, and impossible to filter, count or join. Deciding what the table is, which columns it has, and what one row means is the core skill of modelling.

02The concept

An entity is a kind of thing the business stores data about: customers, products, invoices. Each entity becomes a table.

An attribute is one fact about that thing: a customer's name, channel, region. Each attribute becomes a column, with one data type.

An instance is one particular thing: one customer. It becomes a row.

Rules for good attributes

1. One fact per column. Not "Kano, North West"; use city and region.
2. One value per cell. Not "Malt drink, Chin chin"; that's two rows of something else.
3. The right data type. Numbers you calculate with are numbers; dates are dates; IDs and phone numbers are text or integers you never add up. (08031234567 stored as a number loses its leading zero.)
4. Clear names. customer_name, not name2 or CustNm.

Common data types

Grain is the answer to what does one row represent? Always state it in one sentence:

• customers: one row per customer.
• orders.csv: one row per product on an order (an order line), not one row per order.
• attendance.csv: one row per employee per working day.

Most double-counting mistakes are grain mistakes: summing a customer's credit limit across their order lines, or counting order lines as orders.

03Example

Harbourline's shipments table: one row per shipment. It has 2,683 rows but only 105 different customers, because customers book many shipments:

SELECT COUNT(*)                    AS shipments,
       COUNT(DISTINCT customer_id) AS customers_who_booked
FROM shipments;

04Walkthrough

Designing Kolanut's product table, step by step:

1. Entity: product. Grain: one row per product (one pack size of one item).
2. Attributes: name, category, list price. Brand and pack size could be separate columns if managers filter by them.
3. Types: product_id integer, product_name text, category text, list_price money.
4. Check against a question: "Revenue by category": category must be a clean column with a fixed list of values. ✓

05Practice

06Check your understanding