Basic Principles of Database Construction
Understand what a database is, why a spreadsheet stops being adequate, and the design rules that keep data trustworthy.
By the end of this lesson you can
- Explain how a database differs from a spreadsheet, and when each is right
- Define table, record, field, primary key, and relationship
- Recognize data redundancy and the problems it causes
- Apply the one-fact-per-field rule when designing a table
Lesson Notes
Read through the key concepts before you try the challenge.
When a spreadsheet stops being enough
You maintain the patient contact list at Lakeside Medical Associates.
The list is a spreadsheet with one row per appointment. A patient who has visited eleven times appears in eleven rows, with her address typed eleven times. She moves. Someone updates four of the rows. The practice now holds two addresses for one patient and no way to tell which is current.
Your task: Understand why storing a fact once is the central idea of database design.
A spreadsheet stores a grid of values. A database stores related tables, each describing one kind of thing, linked so that each fact is recorded once. That difference sounds academic until data changes — and data always changes.
In the scenario above, a database would hold one Patients table with one row per patient, and one Appointments table with one row per appointment, linked by a patient ID. The address exists in exactly one place. Changing it changes it everywhere, because everywhere is one row.
Key terms
- Table
- A collection of records about one kind of thing — patients, appointments, providers. One table per kind of thing.
- Record (row)
- One instance: one patient, one appointment.
- Field (column)
- One attribute of that thing: last name, date of birth, appointment date.
- Primary key
- A field whose value uniquely identifies each record. No duplicates, never empty.
- Foreign key
- A field holding another table's primary key. This is what links tables together.
- Redundancy
- The same fact stored in more than one place — the condition that allows a database to contradict itself.
| Situation | Use | Why |
|---|---|---|
| A one-off calculation or budget | Spreadsheet | Fast to build; relationships do not matter |
| Data about several related things | Database | Each fact stored once, linked by keys |
| Records many people update over years | Database | Validation and integrity rules prevent contradictions |
| Ad-hoc analysis of a data extract | Spreadsheet | Better analysis and charting tools |
| Anything where the same value is typed repeatedly | Database | Repeated typing is the signal that a second table is needed |
Splitting one flat list into proper tables
Turn the appointment spreadsheet — patient name, address, phone, appointment date, provider, reason — into a sound table design.
- 1
List the distinct kinds of thing the data describes.
There are three: patients, providers, and appointments. Each becomes a table. Identifying the nouns is the whole of basic database design, and doing it before touching Access saves rebuilding later.
- 2
Give each table a primary key that will never change.
PatientID, ProviderID, AppointmentID — usually auto-numbered. A name is a poor key because names repeat and people change them; a phone number is worse because it changes often. A key's only job is to identify, so it should carry no other meaning.
- 3
Move each fact to the table it actually belongs to.
The address describes the patient, not the appointment, so it belongs in Patients. This is the step that eliminates the eleven copies. If a fact is about the patient, it goes in the patient's row — once.
- 4
Link Appointments to the other tables with foreign keys.
Appointments holds PatientID and ProviderID rather than repeating names and addresses. One appointment row is then a small row of dates and references, and the patient's details are reached through the link.
- 5
Check that every field holds exactly one fact.
A field holding 'Jane Okafor' should be two fields, and one holding '123 Main St, Brooklyn NY 11201' should be several. Combined fields cannot be sorted or filtered reliably — you cannot sort by surname if the surname is buried in a full-name field.
Result: Three tables where each fact is stored once, and a changed address updates everywhere automatically.
One table per kind of thing, one fact per field, a stable key on every table. Repeated typing is the symptom that tells you a table is missing.
In a patient database, why is a patient's phone number a poor choice for a primary key?
Challenge
Apply what you've learned in this lesson.
Design before you build. This exercise needs paper, not Access.
- A clinic tracks: patient name, patient address, insurance carrier, policy number, appointment date, provider name, provider specialty, visit reason, and copay collected. List the distinct kinds of thing this describes.
- Design a table for each, naming its fields and choosing a primary key. Mark which fields are foreign keys.
- Identify every field in the original flat list that would have been typed repeatedly, and say which table now stores it once.
- Find two fields in the original list that hold more than one fact, and split them.
Finished this lesson?
Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.