Design happens before any software is opened
The temptation is to open Access and start making tables. The resulting database usually works for a month and then becomes impossible to change, because decisions were made by accident rather than on purpose.
Design proceeds in three stages, and only the last involves a particular product.
| Stage | Produces | Depends on the software? |
|---|---|---|
| Conceptual design | entities, attributes and relationships — an ER diagram | no |
| Logical design | tables, fields, keys, and normalised structure | no |
| Physical design | data types, indexes, storage and access paths | yes |
Why the first two stages are product-independent
A conceptual and logical design that mentions no product can be implemented in Access, MySQL, Oracle or a spreadsheet, and can be reviewed by someone who knows the school but not the software. That separation is the point: the person who understands the problem and the person who understands the tool are rarely the same person.
Finding the entities and attributes
Start from a written description of what the system must do, and read it grammatically. The nouns are candidate entities and attributes; the verbs connecting them are candidate relationships.
Then sort the nouns. A noun with several properties of its own is an entity and becomes a table. A noun that is simply a property of something else is an attribute and becomes a field. "Student" has a name, an address and a date of birth, so it is an entity. "Address" has no properties of its own here, so it is an attribute of Student — though in a delivery company, where an address has a district, a postcode and access instructions, it might well become an entity.
Design a database from this description: "A clinic records patients and their appointments. Each appointment is with one doctor, at a stated date and time. A patient may have many appointments and a doctor sees many patients."
- Nouns: clinic, patient, appointment, doctor, date, time.Underline them in the question paper before doing anything else.
- Clinic is the system itself, not an entity. Date and time are properties of an appointment, so they are attributes.Discarding non-entities early keeps the design small.
- Entities: PATIENT, DOCTOR, APPOINTMENT — each has properties of its own.APPOINTMENT is an entity in its own right because it has a date and a time.
- Relationships: PATIENT has many APPOINTMENTs; DOCTOR has many APPOINTMENTs.The apparent many-to-many between patient and doctor is resolved by APPOINTMENT, which sits between them.
- PATIENT(PatientID, Name, Phone), DOCTOR(DoctorID, Name, Specialism), APPOINTMENT(ApptID, PatientID, DoctorID, Date, Time).Date and Time belong to the appointment, since they describe that meeting rather than either person.
Three tables, with APPOINTMENT holding both foreign keys plus the date and time.
The clinic above is the Many to many case already resolved: patient and doctor are many-to-many, and APPOINTMENT is the linking table that makes it storable.
Drawing the ER diagram
An entity-relationship diagram is the conceptual design on one page. Entities are rectangles, relationships are diamonds or labelled lines between them, and attributes are ovals or simply listed inside the entity box.
The cardinality — 1:1, 1:M or M:N — is written at each end, and it is the part that actually determines the tables. Getting it wrong produces a design that cannot store the data at all, which is why it is worth stating both directions in words before writing a symbol: "one class has many students; one student has one class."
Say it both ways round
Students decide a relationship is one-to-many after checking only one direction. Test both: "one doctor has many appointments" and "one appointment has one doctor" gives 1:M. But "one student takes many subjects" and "one subject has many students" gives M:N — and that needs a third table. Checking one direction cannot tell the two cases apart.
From diagram to tables
Converting a finished ER diagram is mechanical, which is the reward for doing the design properly.
Every entity becomes a table, with its attributes as fields and a primary key chosen or invented. Every 1:M relationship becomes a foreign key on the "many" side. Every M:N relationship becomes a new linking table containing both primary keys, plus any attribute belonging to the relationship itself. A 1:1 relationship becomes a foreign key on either side — or, more often, a reason to ask whether the two entities should be one table.
Before you leave this chapter
- Conceptual and logical design mention no product; only physical design does.
- Nouns are candidate entities and attributes; verbs are candidate relationships.
- A noun with properties of its own is an entity; otherwise it is an attribute.
- State every relationship in both directions before deciding its cardinality.
- Entity → table, 1:M → foreign key on the many side, M:N → a linking table.
Checking a design before building it
A design that looks reasonable can still be unable to store the data it was made for. Four questions catch almost every fault, and all of them can be answered on paper in a few minutes — which is considerably cheaper than discovering the problem after the forms and reports have been built on top of it.
- Can every fact be stored exactly once? If something has to be typed into two tables, the design has a redundancy that will become an inconsistency.
- Can every question be answered? Walk through each report the system must produce and check the data it needs actually exists somewhere.
- Does every table have a primary key that will never change? A key built from a name or a phone number is a problem waiting for someone to get married or move house.
- Is every M:N relationship resolved? An unresolved one cannot be stored at all, and it is the fault most often missed.
Test the design with real examples
Take three or four genuine records and write them into your tables by hand. A student in two classes, a book with two authors, an order with three items — the awkward cases are where designs break. If you cannot write the example down, the design is wrong, and you have found out before writing any software.