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?

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.

MemberIDNameHouseYearGroupBest50mDateJoinedFeePaid
101Aisha RahmanR841.307/09/2026TRUE
102Sam KhanB938.907/09/2026TRUE
103Sam KhanG745.014/09/2026FALSE
104Leo MarshB939.621/09/2026TRUE
105Priya NairR843.821/09/2026FALSE

(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?

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

How many records and how many fields does the swimmers’ table hold?
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?

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 typeHoldsExample from the club
Text (also called alphanumeric)Any characters: letters, digits, spaces, punctuation. In programming this is the stringName: Aisha Rahman
CharacterExactly one character. In programming this is charHouse: R
BooleanOnly two values: TRUE or FALSE (yes or no). In programming this is BOOLEANFeePaid: TRUE
IntegerA whole numberYearGroup: 8
RealA number that can have a fractional part (the programming name is real number)Best50m: 41.3
Date/timeA date, a time, or bothDateJoined: 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

The House field holds a single letter: R, B or G. Which data type is the best fit?
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

Which data type should the ParentPhone field have, and why?
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.

The data type of each field in the bus-route table
Field nameExample valueData type
RouteLetterC
Seats48
TicketPrice2.75
DepartureTime07:45
WheelchairSpaceTRUE
DriverNameMr J. Patel

    Show the completed table
    Field nameExample valueData type
    RouteLetterCCharacter
    Seats48Integer
    TicketPrice2.75Real
    DepartureTime07:45Date/time
    WheelchairSpaceTRUEBoolean
    DriverNameMr J. PatelText

    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?

    Show what happens

    Other fields might differ (house, year group), but two swimmers could share those too. The safest way is to give every swimmer a value that is different for each record, like the MemberID: 102 and 103. Looking up 102 can only ever find one swimmer.

    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

    Which field of the swimmers’ table is the most suitable primary key?
    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

    A school’s lost-property office stores each found item in a table with the fields ItemNumber, Description, Colour and DateFound. Which field should be the primary key?
    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.

    1. 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.
    2. 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.
    3. 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.
    4. Decide what the computer should check as data is typed (validation, the next step). For example, ReplacementCost must not be negative.

    Apply it

    A music department (fictional) wants a table of the instruments it lends to pupils. It must record an instrument number (a whole number), the name of the instrument, its condition as one letter (G for good, F for fair, R for repair), the date it was bought and its value in pounds and pence. Which data types fit those fields, in the order they are listed?
    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?

    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:

    CheckWhat it testsExample for the swimmers’ table
    Presence checkSomething has been enteredName must not be left empty
    Range checkThe value lies between a lowest and a highest allowedYearGroup must be from 7 to 11
    Length checkThe value has an allowed number of charactersMemberID must have exactly 3 digits
    Type checkThe value is of the right data typeYearGroup 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.

    Which validation check would refuse each entry?
    What is enteredRule for the fieldCheck that refuses it
    YearGroup is 14Must be from 7 to 11
    Name left emptyMust be filled in
    MemberID is 10Must have exactly 3 digits
    YearGroup is “eight”Must be a whole number

      Show the completed table
      What is enteredRule for the fieldCheck that refuses it
      YearGroup is 14Must be from 7 to 11Range
      Name left emptyMust be filled inPresence
      MemberID is 10Must have exactly 3 digitsLength
      YearGroup is “eight”Must be a whole numberType

      Apply it

      The coach types Sam Khan’s year group as 8, but Sam is really in Year 9. The range check for 7 to 11 is in place. What happens?
      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

      A new field, LockerCode, must always be exactly four characters long. Which check refuses “27”?
      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.

      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

      In the canteen table, the field MealCode must always hold exactly three characters, such as V12. A user types V1. Which validation check refuses 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.