Study Guide
Designing a Database Table
How to turn a list of things a club or school needs to remember into a database table: fields and records, the right data type for each field, a primary key that identifies each record, and validation that stops unreasonable data going in.
Lesson 6.1 Databases
For: Cambridge IGCSE Computer Science learners (about ages 14 to 16), or anyone starting to learn how data is stored in tables
Stage: Think
A register on paper
Riverside Swimming Club is a fictional after-school club. Its coach keeps a paper register: one scrappy page per swimmer, with whatever she remembered to write down. Last week she needed to find every swimmer who had not paid, and it took her forty minutes.
Think it through
The coach wants to replace the register with something a computer can search in seconds. What details would she need to record about each swimmer, and how could she lay them out so that every swimmer is described in the same way?
- Hint 1: Think of what she needs to look up: who has paid, who is fastest, who joined when.
- Hint 2: If every swimmer has the same set of details, how could you draw that on paper?
Show what happens
A sensible list has a name, the year group, a fastest time, the date they joined and whether they have paid. Laying it out as a grid, with one row for each swimmer and one column for each detail, means every swimmer is described in exactly the same way, and that is what makes searching quick.
Stage: Discover
Rows and columns
The coach settles on this grid. Every number and name in it is invented for this lesson.
| MemberID | Name | House | YearGroup | Best50m | DateJoined | FeePaid |
|---|---|---|---|---|---|---|
| 101 | Aisha Rahman | R | 8 | 41.3 | 07/09/2026 | TRUE |
| 102 | Sam Khan | B | 9 | 38.9 | 07/09/2026 | TRUE |
| 103 | Sam Khan | G | 7 | 45.0 | 14/09/2026 | FALSE |
| 104 | Leo Marsh | B | 9 | 39.6 | 21/09/2026 | TRUE |
| 105 | Priya Nair | R | 8 | 43.8 | 21/09/2026 | FALSE |
(Best50m is a swimmer’s fastest 50 metres in seconds. House is the school house colour: Red, Blue or Green.)
Think it through
What does one whole row of the grid describe? And what does one whole column describe?
- Hint 1: Look at the row for Aisha: what is it about?
- Hint 2: Look down the YearGroup column: what is the same about every entry?
Show what happens
A row describes one swimmer: everything the club knows about that person. A column describes one kind of detail, such as year group, and holds that detail for every swimmer.
A database is an organised collection of data, stored so that it can be searched, sorted and updated quickly. The simplest database holds its data in a single table: a grid of rows and columns.
- Each row is a record: all the data held about one item. Aisha’s row is one record.
- Each column is a field: one kind of detail, held for every record. YearGroup is one field.
The names at the top of the columns are the field names. A field name is written as one word, so MemberID and not Member ID, so that a computer can read it as a single name.
Check your understanding
Show the answer
Answer: B. 5 records and 7 fields.
Count the rows of swimmers for the records, and the columns for the fields.
Stage: Predict
What kind of value does a field hold?
When the coach adds a swimmer, the computer must know what kind of value each field holds, so that it can store it, sort it and compare it correctly. Look at two fields: YearGroup holds 8, 9, 7; Best50m holds 41.3, 38.9, 45.0.
Think it through
Suppose the coach chose a data type that can hold only whole numbers for Best50m. What would go wrong when she typed in 41.3?
- Hint 1: What happens to the .3?
- Hint 2: Would two swimmers with 41.3 and 41.9 still be told apart?
Show what happens
The computer could not store the fractional part. A time of 41.3 might be rejected, or cut to 41, and the club could no longer tell a swimmer on 41.3 from one on 41.9. A field needs a data type that fits the values it will hold.
Each field is given a data type, and Cambridge IGCSE Computer Science expects you to choose from six. You met most of them in programming; a database uses the same ideas.
| Data type | Holds | Example from the club |
|---|---|---|
| Text (also called alphanumeric) | Any characters: letters, digits, spaces, punctuation. In programming this is the string | Name: Aisha Rahman |
| Character | Exactly one character. In programming this is char | House: R |
| Boolean | Only two values: TRUE or FALSE (yes or no). In programming this is BOOLEAN | FeePaid: TRUE |
| Integer | A whole number | YearGroup: 8 |
| Real | A number that can have a fractional part (the programming name is real number) | Best50m: 41.3 |
| Date/time | A date, a time, or both | DateJoined: 07/09/2026 |
A date/time field is better than text for a date, because the computer can then sort dates into order and work out how many days lie between two of them.
Check your understanding
Show the answer
Answer: A. Character, because every value is exactly one character.
When every value is exactly one character, character is the most precise data type.
Stage: Discover
A number that is not a number
The club also wants to store a parent’s mobile phone number, such as 07700 900123 (a number reserved for use in examples). The coach says: “It is made of digits, so it must be an integer.”
Apply it
Show the answer
Answer: C. Text, because it is never calculated with and must keep its leading 0.
The test is not “is it made of digits?” but “will I ever calculate with it?”. Phone numbers, postcodes and house numbers are never added or averaged, so they are text.
A value belongs to a number data type only when it is a quantity you might count, measure or calculate with, like a year group or a time. A phone number, a postcode or a bank account number is a label that happens to use digits. Stored as text, it keeps its leading zeros and spaces exactly as typed.
Now try the choices yourself, for a different table. A school bus company (fictional) keeps one record for each route. Its first record is shown. Complete the data type of each field, choosing from text, character, Boolean, integer, real or date/time.
| Field name | Example value | Data type |
|---|---|---|
| RouteLetter | C | |
| Seats | 48 | |
| TicketPrice | 2.75 | |
| DepartureTime | 07:45 | |
| WheelchairSpace | TRUE | |
| DriverName | Mr J. Patel |
- Ask of each value: how many characters, and could I calculate with it?
Show the completed table
| Field name | Example value | Data type |
|---|---|---|
| RouteLetter | C | Character |
| Seats | 48 | Integer |
| TicketPrice | 2.75 | Real |
| DepartureTime | 07:45 | Date/time |
| WheelchairSpace | TRUE | Boolean |
| DriverName | Mr J. Patel | Text |
Stage: Discover
Telling records apart
Look back at the swimmers’ table. The coach rings the club’s helper with an urgent message: “Sam Khan has left their goggles at the pool.”
Think it through
Two swimmers in the table are called Sam Khan. How could the computer, or the coach, find the right record without any doubt? What would you add to the table?
- Hint 1: What differs between the two Sam Khan records?
- Hint 2: Could you give every swimmer something that nobody else has?
A field whose value is different in every record, and so identifies exactly one record, is called a primary key. The swimmers’ table uses MemberID. A good primary key has three qualities:
- Unique: no two records ever share a value.
- Always present: every record has one; it is never left empty.
- Stable: it does not change when something about the person changes.
A name fails the first test: it is easy to meet two people with the same name. A year group fails it too, because many swimmers share one. An ID is a value invented for the job. That is why schools give you a student number, and shops give you a customer number.
Check your understanding
Show the answer
Answer: D. MemberID, because each swimmer has a different one.
A primary key is usually an ID that exists only to tell records apart.
Apply it
Show the answer
Answer: B. ItemNumber, because each item has its own number.
The description of an item describes what it is. The item number tells it apart.
Stage: Explain
Defining a table from storage requirements
So far you have chosen one detail at a time. Defining a database means doing all of it from a short list of what must be stored. Here is one worked through. As you read it, notice that every choice has a reason.
The storage requirements. The office must record, for each item handed in: a number for the item, a description such as “black rucksack”, its colour, the room code where it was found (such as S4), the date it was found, how much it would cost to replace in pounds and pence, and whether its owner has claimed it.
- List one field for each detail, with a one-word field name: ItemNumber, Description, Colour, RoomFound, DateFound, ReplacementCost, Claimed. The reason: every record must hold the same details, in the same order.
- Choose a data type for each field, asking “what kind of value, and will I calculate with it?”.
- ItemNumber: integer, because the office will number its items 1, 2, 3 and so on, and the computer can create the next number by adding 1.
- Description and Colour: text, because both are made of letters.
- RoomFound: text, because a code such as S4 mixes a letter and a digit (this is why it is called alphanumeric).
- DateFound: date/time, so the dates can be sorted.
- ReplacementCost: real, because a cost has pence after the point.
- Claimed: Boolean, because the answer is yes or no.
- Choose the primary key. Several items can share a description, a colour or a room, and the office cannot rely on a date, so ItemNumber is the key: different for each item, always present, and never changed.
- Decide what the computer should check as data is typed (validation, the next step). For example, ReplacementCost must not be negative.
Apply it
Show the answer
Answer: C. Integer, text, character, date/time, real.
For each field, ask: is it a whole number, a fractional number, a single character, a date or time, yes or no, or other text?
Stage: Experiment
Stopping unreasonable data
The swimming coach types quickly. Yesterday she entered a new swimmer’s year group as 79.
Think it through
The club only takes pupils in Years 7 to 11. What could the computer do when the coach types 79 as a year group? What could it do if she leaves a name blank?
- Hint 1: Should the computer wait until someone notices the problem next term?
Show what happens
The computer can refuse the value at once and ask for a new one. That is better than storing a mistake that nobody notices. In the same way it can refuse a record with no name.
An automatic check that data is reasonable before it is stored is called validation. Defining a table includes deciding which checks each field needs. Four common ones:
| Check | What it tests | Example for the swimmers’ table |
|---|---|---|
| Presence check | Something has been entered | Name must not be left empty |
| Range check | The value lies between a lowest and a highest allowed | YearGroup must be from 7 to 11 |
| Length check | The value has an allowed number of characters | MemberID must have exactly 3 digits |
| Type check | The value is of the right data type | YearGroup must be a whole number, not “eight” |
Validation checks that data is reasonable, not that it is true. If Aisha is really in Year 8 and the coach types 9, a range check from 7 to 11 accepts it, because 9 is a sensible year group. Validation catches impossible data. Only a person looking at the source can catch a plausible mistake.
| What is entered | Rule for the field | Check that refuses it |
|---|---|---|
| YearGroup is 14 | Must be from 7 to 11 | |
| Name left empty | Must be filled in | |
| MemberID is 10 | Must have exactly 3 digits | |
| YearGroup is “eight” | Must be a whole number |
- Ask: is it the value’s size, its length, its kind, or its absence that is wrong?
Show the completed table
| What is entered | Rule for the field | Check that refuses it |
|---|---|---|
| YearGroup is 14 | Must be from 7 to 11 | Range |
| Name left empty | Must be filled in | Presence |
| MemberID is 10 | Must have exactly 3 digits | Length |
| YearGroup is “eight” | Must be a whole number | Type |
Apply it
Show the answer
Answer: B. It is accepted, because 8 is a reasonable year group, though wrong.
Validation can refuse impossible values; it cannot know the truth.
Apply it
Show the answer
Answer: C. A length check, because “27” has only two characters.
A length check counts characters, a range check compares sizes, and a presence check asks only whether something was entered.
Stage: Consolidate
Design one yourself
Think it through
In your own words, explain why a database table needs a primary key, using an example that is not the swimming club.
Show a model answer
A good answer says that a primary key identifies exactly one record, and gives an example such as: “A cinema’s table of films could have two films with the same title, because one is a remake, so it needs a FilmID. A search for the title would return two records, but a search for FilmID 31 can only return one.”
Think it through
A school canteen (fictional) wants a table of the meals it sells. Write down the fields you would use, a data type for each one, which field is the primary key, and one validation check.
Work this out on paper or the board first. Nothing here is marked: compare yours with the model answer.
Show a model answer
One possible answer: MealID (integer, primary key); MealName (text, presence check); IsVegetarian (Boolean); Price (real, range check so that it is not negative); LastOrdered (date/time). Other designs work, as long as each data type fits its field, the key is unique for every meal, and the check matches the field it protects.
Apply it
Show the answer
Answer: A. A length check, because V1 is two characters long.
The rule counts characters, so a length check applies.
- A database stores data in one or more tables. In a table, each row is a record (one item) and each column is a field (one kind of detail).
- Every field has a data type: text/alphanumeric, character, Boolean, integer, real or date/time. The test is: what kind of value is it, and will I calculate with it? A phone number is text.
- A primary key is a field whose value is different in every record, so it identifies exactly one. It is usually an ID, never a name.
- Validation (presence, range, length and type checks) refuses unreasonable data. It cannot tell whether data is correct.
- The next lesson, Querying a Database with SQL, uses a table like this to ask questions of its data.
Sources and scope
- What this lesson is. The first of two lessons on databases for Cambridge IGCSE Computer Science (0478, Topic 9), written for this site. It is a draft awaiting the site owner’s editorial review. Its curriculum links are provisional: the wording of each linked specification point has been checked against the published specification, but no link has yet been verified by the site owner. The links show where the lesson may help with a course; they do not imply that any examination board has reviewed or endorsed it. They were written against the 0478 syllabus for examination in 2026 to 2028.
- Original material. Riverside Swimming Club, its swimmers, the bus company, the lost-property office, the music department and the canteen are fictional, and so is every record in the tables. The phone number is one reserved for use in examples.
- Simplified on purpose. The lesson covers a database of one table only; databases with several linked tables, and the checks that go with them, are for AS and A Level. Validation is limited to four common checks; verification and other checks are left to other lessons. Real systems often generate IDs automatically, which the lesson mentions only in passing.
The data type (BOOLEAN) with only two possible values, TRUE and FALSE.
The data type (CHAR) for one single character, written between single quotes, such as ‘x’.
The kind of value a variable holds, such as a whole number or text. It decides what the program can do with the value.
An organised collection of data stored so that it can be searched, sorted and updated.
The data type for a date, a time, or both, so that the computer can sort and compare them correctly.
One column of a database table: one kind of detail held about every record, such as a name or a year group.
The data type for whole numbers, positive, negative or zero, such as 5 or -3.
A field whose value is different in every record, so that it identifies exactly one record.
The data type (REAL) for numbers that can have a fractional part, always written with at least one digit either side of the point, such as 4.7 or 0.0.
One row of a database table: all the data held about one item, such as one member or one book.
A language for asking questions of a database and changing it, written as short statements such as SELECT and WHERE.
The data type (STRING) for text: zero or more characters written between double quotes, such as “Hello”.
The part of a database that stores data in rows and columns: each row is a record and each column is a field.
An automatic check, made by a program or a database, that data is sensible and allowed before it is accepted, such as a number being inside a set range. It cannot show that the data is correct.
Your progress on this lesson
Saved in this browser only. It is not sent anywhere, does not follow you to another device, and is lost if you clear this site’s data.
This browser is not allowing the site to save progress, so it will be forgotten when you leave the page.