The Phases of Database Design
Database design is not a single act. It is seven phases, each producing a document that the next one consumes. This lesson maps the rest of the course and explains why the order cannot be reversed.
Beginner
Learning objectives
- Name the seven phases and state the input and output of each
- Explain why the sequence cannot be reversed
- Give the quality criterion for each phase
- Recognise the symptoms of a project that skipped a phase
- Place each artefact of the course (E-R diagram, relational schema, DDL) in its phase
Prerequisites
- Introduction to Databases
- Database Management Systems: Definition and Functions
- Logical and Physical Data Independence
- Relational and Non-Relational Databases
Introduction
Every database that is hard to maintain, slow, or wrong in a way nobody can fix has the same history: a design process that was not a process. Tables were created as the application was written; columns were added as screens needed them; a "refactor" was postponed until it became a rewrite.
The alternative is not more discipline for its own sake. It is a sequence of seven phases, each of which answers one question and produces one document. The document is the contract between the people who decide and the people who implement, and it is what makes the next phase possible.
This lesson is the map of the rest of the course. Modules 2 to 8 are these seven phases, one or two per module.
The seven phases
1. REQUIREMENTS ANALYSIS "What does the organisation need?"
↓ requirements document
2. CONCEPTUAL DESIGN "What things exist and how are they related?"
↓ E-R schema (entities, attributes, relationships, cardinalities)
3. SCHEMA RESTRUCTURING "Which conceptual constructs must be transformed
↓ before a relational translation is possible?"
↓ restructured E-R schema
4. LOGICAL DESIGN "What relations, keys and foreign keys?"
↓ relational schema
5. NORMALIZATION "Does every fact live in exactly one place?"
↓ normalized relational schema
6. PHYSICAL DESIGN "How is it stored so that it is fast and safe?"
↓ DDL + indexes + storage parameters
7. IMPLEMENTATION "How is it built and used?"
↓ running database, SQL, applications
Phase 1 — Requirements analysis (Module 2)
Question: what does the organisation need from this database?
Input: interviews, existing documents, procedures, forms, regulations, the current (possibly paper) system.
Output: a requirements document containing: a description of the domain, a glossary of terms, the functional requirements (what the system must do), the non-functional requirements (volumes, performance, security, retention), and the business rules.
Activities: interviewing, observing, reading, resolving contradictions, writing the glossary.
Quality criterion: every requirement is traceable to a source, and no term has two meanings. If "cliente" means a person in one department and a company in another, the design will fail — and no amount of E-R modelling will fix it.
Symptom of skipping it: the schema contains fields nobody uses and lacks fields everyone needs. Requirements are discovered during implementation, one bug report at a time.
Phase 2 — Conceptual design (Module 3)
Question: what things exist in this domain, with what properties, and how are they related?
Input: the requirements document.
Output: a conceptual schema, expressed as an E-R diagram: entities, attributes, identifiers, relationships, cardinalities, generalisations.
Activities: choosing entities, choosing attributes, choosing relationships, assigning cardinalities, resolving ambiguities with the domain expert, checking the schema against every requirement.
Quality criterion: the schema represents the requirements and nothing else — no storage concerns, no performance concerns, no table-shaped thinking. A conceptual schema should be readable by the domain expert, not only by the programmer.
Symptom of skipping it: the model is a copy of the screens. Concepts that the organisation thinks about every day have no name in the database, and the same concept is modelled twice in two subsystems.
Phase 3 — Schema restructuring (Module 4)
Question: which conceptual constructs must be transformed before a relational translation is possible or convenient?
Input: the conceptual schema.
Output: a restructured conceptual schema — still conceptual, still readable, but with the constructs that the relational model cannot represent directly either eliminated or prepared for translation.
Activities: analysing redundancies, deciding how to handle generalisations (collapse into the parent, push down into the children, replace with relationships), handling multivalued and composite attributes, choosing the primary identifiers.
Quality criterion: every transformation is documented with its motivation and its cost. Restructuring is not a cleanup; it is a set of deliberate trade-offs, and the document must show why each was made.
Symptom of skipping it: generalisations are translated blindly, producing either a proliferation of nearly-empty tables or a single table full of nulls, with no record of the decision. Multivalued attributes become comma-separated strings in a single column.
Phase 4 — Logical design (Module 5)
Question: which relations, with which attributes, which primary keys and which foreign keys, represent this conceptual schema?
Input: the restructured conceptual schema.
Output: a relational schema — a set of relation schemas with keys, plus the referential integrity constraints.
Activities: applying the translation rules (entities → relations, 1:N → foreign key on the N side, N:M → a new relation, and so on), choosing primary keys, deciding on optional and mandatory participation.
Quality criterion: every fact of the conceptual schema is represented exactly once, and every relationship is representable. No information is lost, and no information is invented.
Symptom of skipping it: the translation is done by an ORM generator from the application's class model, so the database is a mirror of the code rather than of the domain. This is the most common failure mode in modern development, and it is invisible until the second application needs the same data.
Phase 5 — Normalization (Module 6)
Question: does every fact live in exactly one place?
Input: the relational schema.
Output: a normalized relational schema — typically in third normal form or Boyce-Codd normal form — with the functional dependencies documented.
Activities: identifying functional dependencies, computing attribute closures, testing the normal forms, decomposing where necessary, verifying that the decomposition is lossless and preserves dependencies.
Quality criterion: no update anomaly is possible, and every remaining redundancy is deliberate and documented.
Symptom of skipping it: the same fact is stored in several rows, and the application contains code to keep them aligned — code that will eventually be forgotten in one place.
Phase 6 — Physical design (Module 7)
Question: how should this be stored so that it is fast enough, safe enough and operable?
Input: the normalized relational schema, plus the non-functional requirements (volumes, response times, concurrency, retention).
Output: DDL for the tables, plus indexes, storage engine and parameters, partitioning, and the access-control plan.
Activities: choosing data types and sizes, defining keys and constraints, choosing indexes based on the expected queries, estimating volumes, evaluating plans with EXPLAIN, planning backup and recovery, defining users and privileges.
Quality criterion: the expected workload meets its response-time targets, and the constraints are all declared. Note that both halves matter: a fast database without constraints is a database that will corrupt itself.
Symptom of skipping it: every column is VARCHAR(255), there is no index except the primary keys, every table is scanned, and the first performance problem is met with "add a server".
Phase 7 — Implementation (Module 8)
Question: how is the database built, populated, used and maintained?
Input: the physical design.
Output: a running database, a migration history, seed data, the queries and views the applications need, and the operational procedures.
Activities: executing DDL, writing migrations, loading initial data, writing and tuning queries, creating views for each user group, granting privileges, testing.
Quality criterion: the implemented database matches the design, and the design documents are updated to match reality. Divergence between documentation and implementation is the beginning of the end.
Why the order cannot be reversed
Each phase's output is the next phase's input. Reversing the order means answering a question without its premises.
| If you start here | What goes wrong |
|---|---|
| From tables | The tables encode an implicit, unexamined conceptual model. Concepts are missing, duplicated or conflated; nobody can tell because nobody wrote the concepts down. |
| From the screens | The database becomes a persistence layer for one application's forms. When a second application needs the same data, it needs a different model, and the two diverge. |
| From the technology | Choosing "documents with embedded arrays" before the conceptual model forces the model into the storage shape. See the previous lesson's warning. |
| From performance | Indexes and denormalisations are added before knowing the queries, so they optimise the wrong things and add constraints that block later correction. |
| From an ORM's generated schema | You get a mirror of the class model: no foreign keys unless the annotations say so, no constraints unless the annotations say so, and a schema nobody has reviewed as a model. |
The pattern is always the same: a phase is skipped, and the missing artefact is reconstructed implicitly by the next phase. Implicit artefacts cannot be reviewed, cannot be tested, and cannot be changed deliberately.
Where the course artefacts belong
| Artefact | Phase | Module |
|---|---|---|
| Requirements document, glossary | 1 | Module 2 |
| E-R diagram | 2 | Module 3 |
| Restructured E-R diagram | 3 | Module 4 |
| Relational schema (relations, keys) | 4 | Module 5 |
| Functional dependencies, normal forms | 5 | Module 6 |
CREATE TABLE, CREATE INDEX, GRANT |
6 | Module 7 |
SELECT, INSERT, views, transactions in use |
7 | Module 8 |
Note that DDL appears in Phase 6, not in Phase 2. Writing CREATE TABLE before the conceptual schema exists is not "starting early"; it is committing to decisions that have not been made.
The case study
Throughout Modules 2 to 8, a single case study accompanies the theory: a library management system. Each module takes it through one phase, always with the same requirements, so that the thread stays coherent:
| Module | What happens to the library |
|---|---|
| 2 | Requirements are collected and analysed: who borrows, what is lent, what the rules are |
| 3 | The E-R schema is built: Socio, Opera, Copia, Prestito, with cardinalities and generalisations |
| 4 | The schema is restructured: generalisations resolved, multivalued attributes handled |
| 5 | The relational schema is derived: relations, primary keys, foreign keys |
| 6 | The schema is normalized: functional dependencies and normal forms |
| 7 | The MySQL tables are created: types, constraints, indexes |
| 8 | The database is used: data loaded, queries written, views created |
The model does not change between modules without an explicit, explained reason. That coherence is itself part of the lesson: a database design is a narrative from requirements to SQL, and a narrative with contradictions is a failed design.
Which phase is missing?
Each of the following statements describes a project. Identify which phase was skipped or done badly, and the concrete consequence.
- "We don't need a requirements document, the client told us what they want."
- "The E-R diagram was drawn after the tables, to document them."
- "We have a generalisation
Persona → Socio, Dipendente, but in the database there is only one tablepersonawith all the columns and no record of the decision." - "The translation was automatic from our Java entities."
- "The table
prestitohastitoloOperaandnomeSociocolumns; the code keeps them updated." - "Every column is
VARCHAR(255); we'll fix the types later." - "The database is in production; the E-R diagram in the repository is from two years ago."
Show the commented solution
1. Phase 1 — requirements analysis. "The client told us" is a conversation, not a requirements document: it has no glossary, no volumes, no non-functional requirements, no traceability, and no record of the questions nobody asked. The consequence appears late: fields missing from the schema, rules nobody knew about, and volumes that break the design (a "small" library with 400 000 loans a year changes the physical design).
2. Phase 2 — conceptual design, done in the wrong order. An E-R diagram drawn from the tables is a description of the implementation, not a design of the domain. It inherits every modelling error of the tables and adds a false sense of documentation. It cannot be used to validate anything with the domain expert, because it says what was built, not what was needed.
3. Phase 3 — restructuring, undocumented. Collapsing a generalisation into the parent is a legitimate choice when most rows are of one type and the differences are small. But it is a trade-off, and the document must record: why this strategy, what was gained, what was lost (the ability to enforce subtype-specific constraints), and which requirements now depend on application logic. Without the record, the next developer cannot know whether the single table is a decision or an oversight, and cannot safely change it.
4. Phase 4 — logical design, delegated to a generator. The translation from a class model is not the translation from a conceptual schema. The class model is shaped by the application's needs (inheritance, lazy loading, framework conventions), not by the domain. Typical consequences: missing foreign keys where the annotation was forgotten, a join table with no unique constraint, NULL-heavy tables from single-table inheritance. See the previous lesson's warning about choosing technology first.
5. Phase 5 — normalization. titoloOpera and nomeSocio in prestito are uncontrolled redundancy: the title exists in opera and again in every loan, and "the code keeps them updated" is the exact definition of an unowned invariant. The consequence is an update anomaly the first time a code path forgets. Note also that a historical title (the title as it was on the loan date) is a legitimate fact — but it must be modelled as such, deliberately, not as a copy.
6. Phase 6 — physical design. VARCHAR(255) everywhere is not a neutral default: it disables length validation, wastes space in indexes, makes CHECK constraints impossible, and hides the difference between a code, a name and a date. "We'll fix it later" on types means a migration on a table that by then has millions of rows — and, worse, means the semantic constraints were never enforced, so the data contain values that will fail when the types are finally corrected.
7. Phase 7 — implementation, without feedback to the design. The implementation has diverged from the documented design. The diagram is now worse than useless: it is misleading. The discipline is that every change applied to the database is reflected in the conceptual and logical documents in the same change. This is the reason ERMap keeps the E-R diagram as a first-class artefact rather than as a one-off drawing.
What this exercise teaches. In every case the missing artefact is not "extra work": it is the thing that would have made the next decision possible. The cost of the missing phase is paid later, with interest, by someone who has less information.
Module 1 in review
Module 1 established the vocabulary and the landscape. Before moving to requirements analysis, these are the ideas that everything else builds on:
| Idea | Where it was established |
|---|---|
| Data, information, knowledge | Lesson 1 |
| Information systems vs computer systems | Lesson 2 |
| The nine problems of file archives | Lesson 3 |
| Database = permanent, shared, organised, controlled, self-describing | Lesson 4 |
| DBMS functions, ACID, what a DBMS does not do | Lesson 5 |
| Logical and physical data independence | Lesson 6 |
| The ANSI/SPARC three schemas and two mappings | Lesson 7 |
| Relational vs document, key-value, column-family, graph; CAP; BASE | Lesson 8 |
| The seven design phases and why the order matters | Lesson 9 |
Module 1 — consolidating exercise
A small dental practice wants a database. The dentist tells you:
"We have about 1 200 patients. Each patient has a file with their personal data, their medical history and their appointments. We have four dentists and two hygienists. Appointments are 30 or 60 minutes. Some patients are minors, so we need the parent's data. We send reminders by SMS. We need to know, for each appointment, who performed it and in which room. There are three rooms. Some treatments are done in several sessions and the price is agreed in advance. We also need to bill the national health service for some treatments and private patients for others. The accountant asks for a monthly report of revenue per dentist."
Answer the following, referring explicitly to the lessons of Module 1.
- Which of the nine problems of file archives would appear if this were implemented with four separate spreadsheets (one per dentist)?
- Write down three non-functional requirements that this description implies but does not state.
- Give one example of a term in this description that is probably ambiguous, and say which phase must resolve it.
- "Some treatments are done in several sessions and the price is agreed in advance" — which phase decides whether this becomes an entity, an attribute or a relationship? Why not the logical design phase?
- The accountant wants "revenue per dentist per month". Is this a fact to store, or a fact to derive? Justify using the concept of controlled redundancy.
- Name the two constraints that this description implies and that a DBMS can enforce declaratively, and one that it cannot.
Show the commented solution
1. With four spreadsheets: redundancy (the same patient's data in up to four files), inconsistency (an address updated in one file only), difficulty of access (the accountant's monthly report needs a new spreadsheet each month), data isolation (the four files have no shared structure, so nothing can be joined), integrity (nothing prevents an appointment for a patient who does not exist, or two appointments in the same room at the same time), concurrency (two receptionists booking the same slot), and security (a spreadsheet contains all patients' medical histories, so anyone who can open it sees everything). Atomicity and backup problems also appear, in the form of a file lost on a single laptop. This is Lesson 3, exactly.
2. Three non-functional requirements implied but not stated:
- Volumes and growth: 1 200 patients now, how many appointments per year? A practice with 1 200 patients has roughly 8 000–12 000 appointments a year. This determines whether an index is enough or the design needs more.
- Concurrency: how many receptionists book simultaneously? Two, or eight across two locations? This determines the isolation strategy for slot booking.
- Retention and privacy: medical records have legal retention periods (typically 10 years or more in most European jurisdictions), and health data are special-category data under the GDPR, which implies encryption, access logging and a defined erasure policy. None of this is in the dentist's sentence, and all of it constrains the design.
- (Others: response time at the desk, SMS provider integration and delivery guarantees, availability during opening hours, backup RPO/RTO.)
3. "Treatment" is the obvious candidate: it may mean a type of treatment (a filling, a root canal — a catalogue item with a price), a session (one visit performing part of a treatment), or the whole course of care (three sessions of the same treatment). The three are different entities with different relationships, and the sentence "the price is agreed in advance" only makes sense for the whole course. Also "patient" — a minor's record: is the parent a patient, a separate person, or a guardian attribute? These ambiguities are resolved in Phase 1 (requirements analysis), with the domain expert, and they must be written into the glossary.
4. Phase 2 (conceptual design). The decision "a treatment course consists of several sessions, and the price belongs to the course, not to the session" is a statement about the domain: it says what things exist and how they relate. It has nothing to do with tables. The logical design phase translates that decision into relations; it cannot make it, because by then the conceptual schema is fixed and the translation rules are mechanical. If the conceptual schema says "a treatment is one thing with a price and a date", the logical design will faithfully produce a wrong database.
5. A fact to derive — with a caveat. Revenue per dentist per month is computable from appointments, treatments, prices and the dentist who performed them. Storing it as a table would be redundancy, and it would be uncontrolled redundancy if any code path could write it. It is legitimate to store it as a controlled redundancy (a materialised monthly summary) if the report is expensive and the maintenance is owned by a single mechanism — a nightly job that recomputes from the source, never an incremental update from the application. The test is the one from Lesson 4: is there exactly one place that writes it, and can the copies diverge? Note also a subtlety: the price agreed in advance must be stored (it is a fact, and it is historical — the price list may change), while the revenue must be derived from it.
6. Declaratively enforceable:
- Referential integrity: an appointment must refer to an existing patient and an existing dentist (
FOREIGN KEY). - Uniqueness / no double booking: no two appointments in the same room at the same time. This is expressible as a
UNIQUEconstraint on(room, startTime)if appointments cannot overlap — and note the subtlety that a 60-minute appointment starting at 10:00 conflicts with a 30-minute one starting at 10:30, which a simpleUNIQUEdoes not catch. That requires either an exclusion constraint (not in MySQL), a trigger, or a design in fixed slots. - Domain / check: the duration is 30 or 60 minutes (
CHECK), the price is non-negative, the appointment end is after the start.
Not declaratively enforceable in a portable way:
- "A minor must have a guardian recorded" — a cross-row, conditional rule: it needs a trigger or application logic, because
CHECKcannot query other rows. - "A treatment course's sessions must not exceed the agreed number" — an aggregate constraint across rows.
- "Appointments cannot overlap for the same dentist" — an interval constraint, which is the same problem as the room one.
What this exercise teaches. Every question maps to a specific lesson, and the answers show the same pattern: the phase that is skipped is the phase whose question was never asked. The dentist's five sentences contain at least three ambiguities, four unstated non-functional requirements, and two constraints that no declarative mechanism can express. None of that is a failure of the dentist; all of it is the work of design.
Where to go next
- Requirements Analysis: Module 2 begins — from the dentist's five sentences to a requirements document.
- Introduction to Databases: the definition everything else rests on.
- The ANSI/SPARC Three-Level Architecture: which schema each phase produces.
- Student and Course: a complete, small E-R diagram you can open in the editor — the shape of the artefact Phase 2 produces.