Designing a Database: Relationships, Normalisation, Indexes & Views
Turn a real problem into tables: entities and relationships (one-to-many, many-to-many), drawing an ER diagram, normalisation (each fact in one place), indexes that make queries fast, query plans, and views.
From a problem to tables
Designing a database starts with words, not SQL. Describe what the system stores, then underline the things (entities): "A student enrols in many courses; each course is taught by one tutor." Each entity becomes a table; each fact about it becomes a column; each relationship becomes a key.
Three kinds of relationship
| Relationship | Example | How to store it |
|---|---|---|
| one-to-many | one customer, many orders | a foreign key on the "many" side: orders.customer_id |
| many-to-many | orders contain many products; a product is in many orders | a junction table with both keys: order_items(order_id, product_id, …) |
| one-to-one | a user and their private settings | a foreign key that is also UNIQUE (or the same table) |
An ER diagram (entity-relationship diagram) draws tables as boxes and relationships as lines. The SQL playground draws one for its databases in the Schema tab: look at how order_items connects orders and products.
Normalisation: each fact in one place
A badly designed table repeats facts. Imagine one big orders sheet with the customer's name and city on every order: change a city and you must find every copy; delete a customer's last order and you lose the customer. Normalisation fixes this by splitting tables so each fact is stored once. In practice, three rules ("normal forms") cover most cases:
- 1NF: one value per cell (no "red, blue" lists in a column; use a separate table), and a key for every row.
- 2NF: every column depends on the whole key (in order_items, the product name depends only on product_id, so it belongs in products).
- 3NF: no column depends on another non-key column (city → country belongs in a cities table, if you care).
Analytics databases sometimes repeat data on purpose (denormalise) to make reports faster. That is a deliberate trade-off, not an accident.
Indexes: making look-ups fast
Without help, finding rows with WHERE customer_id = 42 means reading every row: a full table scan. An index is a sorted copy of one or more columns with pointers to the rows, like the index at the back of a book, so the database can jump straight to the matches.
Index the columns you often filter, join or sort on (foreign keys especially). Do not index everything: each index takes space and slows down inserts and updates. Primary keys and UNIQUE columns are indexed automatically. PostgreSQL shows plans with EXPLAIN (or EXPLAIN ANALYZE, which also runs the query and shows the real times).
Views: saved queries
A view is a query with a name that you can use like a table. It stores no data: it runs the query each time. Views hide complexity and give everyone the same definition of a number:
Try it: indexes and views
Run the query plan before and after creating the index, then use the view like a table.
Interactive: this part runs in your browser.
Practice: check yourself
Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Create a view order_totals with order_id and total (sum of quantity × unit_price per order). The checker queries your view.
Create an index called idx_items_product on order_items(product_id).
Summary
Key words
- entity
- a kind of thing the database stores, one table each
- one-to-many
- stored with a foreign key on the "many" side
- many-to-many
- stored with a junction table
- ER diagram
- a drawing of tables and their relationships
- normalisation
- splitting tables so each fact is stored once
- index
- a sorted structure that makes look-ups fast
- query plan
- how the database will run a query (EXPLAIN)
- view
- a saved query used like a table
Common mistakes
- Lists in a cell ("Python, SQL") instead of a separate table.
- Copying the same fact into several tables, so copies drift apart.
- Forgetting indexes on foreign keys, so joins get slow as data grows.
- Indexing every column "just in case".
- Expecting a view to store data or be faster on its own.
Quiz
1. How do you store a many-to-many relationship?
2. What problem does normalisation solve?
3. What does an index speed up?
4. What does a view store?