FREE PREVIEW · MODULE 1 · LESSON 1 · 15 MIN

Tables, rows and keys

How relational tables are organised, what primary and foreign keys guarantee, and why you should name the grain of every table before you query it.

A relational database stores data in tables. Each table describes one kind of thing, each row is one instance of it, and each column is one attribute with a fixed data type, such as integer, text, numeric or date. This course uses one fictional schema of four tables that fits almost any business selling products: customers, orders, order_items and products. A customer places orders, each order contains one or more items, and each item refers to one product. The worked examples apply this same structure to different fictional shops, so every query you learn carries straight over to the next lesson.

The columns are deliberately few. customers holds customer_id, name, email, country, channel and signup_date. orders holds order_id, customer_id, order_date, status, discount_code and shipping_fee. order_items holds order_item_id, order_id, product_id, quantity and unit_price, the price actually charged. products holds product_id, product_name, category and list_price. Each table has a primary key: a column whose value is unique and never empty, such as order_id. The database enforces it, so two orders can never share an id, and COUNT(*) and COUNT(DISTINCT order_id) on orders must return the same number.

A foreign key is a column that points to the primary key of another table. orders.customer_id refers to customers.customer_id, order_items.order_id refers to orders.order_id, and order_items.product_id refers to products.product_id. These links describe relationships, and relationships decide how joins behave. One customer can have many orders and one order can have many items, which are one-to-many relationships. Learn them from the schema diagram or the table definitions, not from column names alone. A column called account_id in two tables may mean two different things, and some data warehouses do not enforce primary or foreign keys at all.

The most useful habit to build now is naming the grain of each table: what one row represents. The grain of orders is one row per order; the grain of order_items is one row per product line within an order. Revenue lives in order_items, as quantity times unit_price, while status and order_date live in orders. To sum revenue for completed orders you need both tables, and you need to know that an order with three items appears three times once they are joined. If a table has no declared or enforced key, check uniqueness yourself before trusting any total built on it.

Example: a tea retailer in Edinburgh checks the grain of its order data

Fernleaf Teas, a fictional online tea shop in Edinburgh, was about to build an average order value report on order_items and first checked what one row meant: SELECT COUNT(*) AS row_count, COUNT(DISTINCT order_id) AS order_count, COUNT(DISTINCT order_item_id) AS item_count FROM order_items; The query returned one row: 18,240 rows, 7,415 distinct orders and 18,240 distinct item ids. Because the row count matched the item ids but not the orders, the grain was one row per order line. The analyst concluded that any order count from this table needs COUNT(DISTINCT order_id), and found that an older report, which divided revenue by COUNT(*), had understated average order value by a factor of about 2.5.

Illustrative example: the business, people and figures are fictional.

Key takeaways

Each table describes one kind of thing; each row is one instance with typed columns
Primary keys are unique per row; foreign keys point to another table’s primary key
State the grain of every table, such as one row per order line, before you query it

Exercise

Create the four course tables in SQLite or PostgreSQL using the statements in the SQL cheat sheet and add a handful of rows, or open a database you can read at work. For each table, write down its primary key, its foreign keys and its grain in one sentence. Then run COUNT(*) and COUNT(DISTINCT key) on each table and confirm that the two numbers match.

Continue with 11 more lessons, module quizzes and 3 downloads.

Write, check and explain the SQL behind everyday business questions, from monthly revenue to retention cohorts. One payment of $119, lifetime access, 14-day refund policy.

See the full course