How relational databases work — tables, keys and types
Before writing SQL, understand what a database is, how tables relate through primary and foreign keys, and why data types matter.
This lesson builds on: Data sources, structures and data quality.
- Explain what a relational database, table, row, column and schema are
- Identify primary keys and foreign keys in a schema and explain what they guarantee
- Choose an appropriate data type for a column and spot type-related errors
- Read the Orion Retail schema and describe how its tables connect
What a database is
A database is an organised, persistent collection of data managed by software called a database management system (DBMS) — for example PostgreSQL, Microsoft SQL Server, MySQL, Oracle or SQLite. A relational database stores data in tables and lets you combine tables through shared values.
SQL (Structured Query Language, pronounced "S-Q-L" or "sequel") is the language used to ask relational databases questions. It is declarative: you describe what result you want; the database decides how to compute it.
The practice databases run on SQLite, directly inside OrionStats. Almost everything you learn is standard SQL that works in PostgreSQL, SQL Server and MySQL. Where dialects differ (dates, LIMIT vs TOP), lessons show both.
Tables, rows, columns and schemas
- A table stores one kind of thing — customers, orders, products.
- A row (record) is one instance of that thing — one customer.
- A column (field) is one attribute — the customer's city.
- A schema is the design of the database: which tables exist, their columns, types and relationships. (In PostgreSQL and SQL Server "schema" is also a namespace that groups tables, e.g.
sales.orders.)
Here are the first rows of the stores table:
SELECT * FROM stores;| store_id | store_name | city | region | open_date | close_date | size_sqm | channel |
|---|---|---|---|---|---|---|---|
| 1 | Orion Baku Central | Baku | East | 2019-04-01 | NULL | 1200 | store |
| 2 | Orion Baku Riverside | Baku | East | 2020-09-15 | NULL | 850 | store |
| 3 | Orion Ganja | Ganja | West | 2021-03-01 | NULL | 700 | store |
| 4 | Orion Sumqayit | Sumqayit | East | 2018-11-10 | NULL | 640 | store |
| 5 | Orion Istanbul Kadikoy | Istanbul | Central | 2022-02-01 | NULL | 1500 | store |
| 6 | Orion Ankara | Ankara | Central | 2024-04-01 | NULL | 900 | store |
| 7 | Orion Tbilisi | Tbilisi | West | 2023-08-01 | NULL | 780 | store |
| 8 | Orion Online | Online | Online | 2018-01-01 | NULL | NULL | online |
| 9 | Orion Baku Airport | Baku | East | 2017-06-01 | 2024-06-30 | 220 | store |
Each row is a store; each column describes it. Notice that close_date is empty (NULL) for stores that are still open, and size_sqm is NULL for the online store — NULL means "no value", and Module 2 has a full lesson on it.
Primary keys: every row has an identity
A primary key is a column (or set of columns) whose value uniquely identifies each row and is never NULL. In stores it is store_id; in orders it is order_id.
Why it matters: without a unique identifier you cannot reliably refer to one specific row, update it, or join it to other tables. Names are not good keys — two customers can both be called "Aylin Aliyev".
Let's check that customer_id really is unique:
SELECT COUNT(*) AS rows_in_table,
COUNT(DISTINCT customer_id) AS distinct_ids
FROM customers;| rows_in_table | distinct_ids |
|---|---|
| 4000 | 4000 |
The two numbers are equal, so every row has a different customer_id.
Foreign keys: how tables connect
A foreign key is a column in one table that refers to the primary key of another table. In orders, customer_id is a foreign key to customers.customer_id, and store_id points to stores.store_id. That reference is what lets you answer "which city do our online customers live in?" by combining tables.
The Orion Retail schema, simplified:
| Table | Primary key | Foreign keys | Grain (one row = …) |
|---|---|---|---|
customers |
customer_id | — | one customer |
stores |
store_id | — | one store (8 = online) |
products |
product_id | — | one product variant |
orders |
order_id | customer_id → customers, store_id → stores, campaign_id → campaigns | one order |
order_items |
order_item_id | order_id → orders, product_id → products | one product line in an order |
sales_targets |
(store_id, year_month) | store_id → stores | one store-month plan |
sales_targets has a composite key: neither column is unique alone, but the pair is.
When the database enforces foreign keys, it refuses an order whose customer_id does not exist. Many analytics warehouses do not enforce constraints for loading speed — so analysts must check for "orphan" rows themselves (you will do this in the data validation lesson).
Data types
Every column has a data type that controls what it can hold and how it behaves:
| Category | Common types | Examples in Orion |
|---|---|---|
| Integer | INTEGER, BIGINT, SMALLINT |
order_id, quantity |
| Decimal | NUMERIC(10,2), DECIMAL, REAL/FLOAT |
unit_price, discount_pct |
| Text | VARCHAR(n), TEXT, CHAR(n) |
city, product_name |
| Date / time | DATE, TIMESTAMP, DATETIME |
order_date |
| Boolean | BOOLEAN (or 0/1 integers) |
is_weekend |
Types matter for correctness:
- Money: use exact
NUMERIC/DECIMALin production systems; floating-point types can produce tiny rounding errors (0.1 + 0.2 = 0.30000000000000004). - Dates stored as text sort correctly only in ISO format (
2025-03-04).04.03.2025sorts wrongly as text — you saw that problem incustomers_raw. - IDs stored as text (
'00123') do not equal integers (123) in strict databases.
'9' > '10' is true when compared as text, because the comparison is character by character. If an amount column is stored as text, ORDER BY amount gives nonsense. Convert with CAST(amount AS NUMERIC).
Practice
In order_items, which column is a foreign key?
sales_targets has one row per store per month. Which is its primary key?
A column amount stores invoice values as text. What happens with ORDER BY amount DESC?
Summary
- A relational database stores data in tables; SQL describes the result you want.
- Primary keys uniquely identify rows; foreign keys reference primary keys in other tables and make joins possible.
- Know the grain of each table before combining it with others.
- Data types determine correct sorting, comparison and arithmetic; money needs exact decimals and dates need real date types or ISO text.