Database Design
What is Database Design
Every application that stores data — from a hospital managing patient records to a retailer tracking thousands of orders — relies on a database. But a database that simply exists is not enough. The way that database is structured determines whether queries run in milliseconds or minutes, whether data stays accurate or drifts into inconsistency, and whether the system can grow as the business grows. That structure is the product of database design.
This lesson defines what database design is, explains the decisions it covers, and introduces the three-level design process that all subsequent lessons in this course will build on.
Definition
Database design is the process of defining the structure, organisation, and constraints of a database so that it accurately represents the real-world domain it models, stores data without redundancy or contradiction, and supports the queries and operations the application needs — both now and as requirements evolve.
That definition contains several important ideas worth unpacking. Database design is a process — not a single decision but a sequence of steps, each producing a more detailed and concrete blueprint. It involves structure — deciding what tables exist, what columns they contain, and what data types those columns hold. It involves organisation — deciding how tables relate to one another and how data flows between them. And it involves constraints — rules that enforce correctness directly inside the database rather than relying on application code to maintain it.
What Database Design Covers
Database design spans several interconnected concerns. A good design addresses all of them — a poor design typically neglects one or more.
| Concern | What it means | What goes wrong without it |
|---|---|---|
| Data modelling | Identifying what real-world things need to be represented and what facts about them matter | Important data is missing; irrelevant data clutters the schema |
| Structure | Organising data into tables and columns with appropriate data types | Data is stored in the wrong format; queries become complex workarounds |
| Relationships | Defining how tables connect — which customer placed which order, which employee works in which department | Data becomes disconnected; joins are impossible or produce incorrect results |
| Integrity | Applying rules — primary keys, foreign keys, NOT NULL, CHECK constraints — that prevent invalid data from entering the database | Orphaned records, duplicate rows, and impossible values accumulate silently |
| Normalisation | Eliminating redundant data by ensuring each fact is stored in exactly one place | The same fact is stored in multiple places; updates make them inconsistent |
| Performance | Structuring data and choosing indexes so that queries return results efficiently | Queries that should take milliseconds take seconds or minutes |
The Three Levels of Design
Database design is carried out in three progressive stages, each building on the previous. These stages are explored in full in Lesson 9, but it helps to have the complete picture from the start.
Conceptual design — What does the business need to store? At this stage you identify entities (the things that matter — customers, products, employees) and the relationships between them. The output is an Entity-Relationship diagram. No database technology is involved yet — this is pure business logic.
Logical design — How should the data be organised? The ER diagram is translated into a relational schema: tables, columns, primary keys, foreign keys, and constraints. This stage applies normalisation rules to eliminate redundancy. The output is still technology-independent — it describes structure, not implementation.
Physical design — How will it actually be stored and accessed? The logical schema is implemented on a specific database platform — Oracle, PostgreSQL, MySQL, SQL Server. Decisions about data types, indexes, tablespaces, and partitioning are made here. The output is the actual CREATE TABLE statements that build the database.
A Real-World Example
Consider an online bookshop that needs to store information about books, customers, and orders. Without a design process, a developer might create a single table that holds everything together:
| order_id | customer_name | customer_email | book_title | author | price | qty |
|---|---|---|---|---|---|---|
| 1001 | Sarah Mitchell | [email protected] | Database Design Essentials | J. Smith | 29.99 | 1 |
| 1002 | Sarah Mitchell | [email protected] | SQL for Beginners | A. Patel | 19.99 | 2 |
| 1003 | James Okafor | [email protected] | Database Design Essentials | J. Smith | 29.99 | 1 |
This table works — until Sarah changes her email address. Now two rows need updating. If only one is changed, the data becomes contradictory. If the book price changes, every row containing that book must be found and updated. These are called update anomalies — the exact problem database design exists to prevent.
A proper design separates this into three tables: Customers (customer_id, name, email), Books (book_id, title, author, price), and Orders (order_id, customer_id, book_id, qty, order_date). Sarah's email is stored once in Customers. The book price is stored once in Books. An order row contains only the IDs that link to those tables. Updating Sarah's email is one row change. Updating a book price is one row change. The data stays consistent — by design.
What Good Design Achieves
A well-designed database delivers four properties that a poorly designed one cannot reliably provide.
| Property | Description |
|---|---|
| Data integrity | Every value in the database is valid. Constraints prevent impossible states — a payment cannot reference an order that does not exist; a quantity cannot be negative. |
| Minimal redundancy | Each fact is stored in exactly one place. Updates change one row, not dozens. Contradictions cannot arise because there is only one version of each fact. |
| Query efficiency | The structure supports the queries the application needs. Indexes are placed where they help most. Reports that aggregate millions of rows return in seconds. |
| Maintainability | New requirements — a new product category, a new type of user, a new relationship — can be accommodated with additive changes rather than destructive rewrites. |
Database Design vs Database Administration
These two disciplines are often confused but are distinct roles with different concerns. A database administrator (DBA) manages a running database — backups, performance tuning, user access, patching, and availability. A database designer defines the structure of the database before and during its construction. In practice, especially on smaller teams, the same person does both — but the skills are different. Design requires understanding of data modelling, normalisation theory, and the application's business logic. Administration requires knowledge of the database engine, operating system, and infrastructure.
This course is entirely focused on design. By the end you will be able to take a set of business requirements, model the data correctly, produce a fully normalised schema, and make informed decisions about physical implementation — the foundation on which everything a DBA manages is built.
Summary
| Concept | Key Point |
|---|---|
| Database design | The process of defining structure, organisation, and constraints of a database |
| Conceptual design | Identifies entities and relationships — produces an ER diagram, technology-independent |
| Logical design | Translates the ER model into tables, keys, and constraints — still technology-independent |
| Physical design | Implements the schema on a specific platform with real CREATE TABLE statements |
| Data integrity | Constraints enforce valid data directly in the database — not just in application code |
| Normalisation | Stores each fact in exactly one place — prevents update anomalies and contradictions |
| Design vs Administration | Design defines structure — administration manages a running database |
Practice Questions
Practice 1. In your own words, what is the difference between a database and a well-designed database?
Practice 2. A bookshop stores all data in a single table. What specific problem arises when a customer changes their email address?
Practice 3. Name the three levels of database design in order and state what each one produces as its output.
Practice 4. What is the difference between database design and database administration?
Practice 5. Which of the four properties of good design is most directly addressed by normalisation?
Quiz
Quiz 1. Which stage of database design produces an Entity-Relationship diagram?
Quiz 2. A fact is stored in three different rows across two tables. Updating it requires changing all three rows. Which design property is being violated?
Quiz 3. At which design level are decisions about indexes and storage made?
Quiz 4. Which constraint type prevents a payment row from referencing an order that does not exist?
Quiz 5. Which of the following best describes the output of logical design?
Next up — Why Database Design is Important — Explore the real cost of poor design through concrete examples of data anomalies, performance problems, and systems that became impossible to maintain.