Why database design matters

Code can be refactored in an afternoon. A schema with millions of rows and many applications depending on it is much harder to change. That is why the decisions you make when designing tables have a long shadow.

What good design gives you

• Correctness: each fact is stored once, so it cannot contradict itself. If a patient's phone number lives in one place, there is no question which copy is right.
• Integrity: constraints stop invalid data at the door, no matter which application or script writes it.
• Flexibility: well structured data can answer questions nobody anticipated, with a query rather than a rewrite.
• Performance: clear keys and relationships make indexing straightforward and queries predictable.

What poor design costs

Consider a simple appointments table that stores the patient's name, phone and email on every appointment row. It works at first. Then:

• A patient updates their phone number. Old appointments keep the old number, new ones get the new one. Which is right?
• Two staff members type the patient's name slightly differently. Reports now show two patients.
• A patient asks to be deleted under privacy law. Their details are scattered across hundreds of rows.

These are called anomalies: update, insert and delete problems caused by storing the same fact in several places. Normalisation, which we cover in section 2, exists to prevent them.

Design is about the domain, not the database

The best schemas reflect how the business actually works. That means talking to people who understand the domain, collecting real examples and asking awkward questions:

• Can a practitioner work at more than one clinic?
• Can an appointment involve more than one service?
• What happens to past appointments when a service is discontinued?
• Are prices fixed, or can they change after booking?

Each answer changes the design. Getting them early is far cheaper than discovering them after launch.

The case study

Throughout this course we design a platform where independent clinics list practitioners and services, and patients book and pay for appointments online. You will receive requirements in stages. After each stage, you update the model and check that the earlier decisions still hold.

A process you can repeat

1. Gather requirements and real examples.
2. Identify entities and relationships.
3. Draw an ER diagram and review it with domain experts.
4. Normalise, then choose keys and constraints.
5. Write the schema and test it with realistic data and queries.
6. Plan how it will evolve.

The rest of section 1 covers steps 2 and 3.