A3.2.3 · Database design
Data types
By the end you can
Outline the data types a relational database stores and choose the right one for an attribute.
Each column of a table has a that tells the database what sort of value goes there. It matters for three reasons: the type decides what you can do with the data (you can add numbers, not text), it controls how much space a value takes, and it lets the database reject nonsense such as the word “tomorrow” in a price column.
The types you need#
| Type | Holds | Example |
|---|---|---|
| CHAR(n) | Text of exactly n characters (fixed length) | GB, A1B2C3 |
| VARCHAR(n) | Text up to n characters (variable length) | Ana Lopez |
| TEXT | Long text (a description, a review) | A long paragraph… |
| INT | Whole numbers | 48575392 |
| REAL / DECIMAL | Numbers with a fractional part | 12.50 |
| DATE | A calendar date, written year-month-day | 2025-06-23 |
| TIME | A time of day | 17:45:00 |
| BOOLEAN | True or false (stored as 1 or 0) | TRUE |
Some systems add others: ENUM limits a column to a list of allowed values (a title: Ms, Mr, Dr), SET allows several from a list, and BIT stores bits. The principle is the same: pick the narrowest type that fits the data.
Choosing well#
- CHAR or VARCHAR? Use CHAR when every value has the same length (a 6-character ID); it is simple and fast to index. Use VARCHAR when lengths vary (names, addresses), so you do not waste space.
- Number or text? If you will calculate with it, use a number. If it is an identifier that happens to be made of digits (a phone number, a postcode, a student ID that can start with 0), store it as text: you never add phone numbers, and a number would lose the leading zero.
- Dates are not text. In a DATE column the database can sort in time order, find the dates between two others and work out differences. Stored as text, “1 July” sorts before “2 January”.
- Keep types consistent between the database and the program that uses it. If one calls an attribute an integer and the other text, inserts fail and comparisons go wrong.
The number of seats in a cinema screen.
The price of a ticket in euros and cents.
A customer’s email address.
Whether a playlist has been downloaded to the phone.
The day a book is returned.
A two-letter country code such as ES.
0 of 6 matched
Check yourself
Data types
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
4 questions · 12 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.State a suitable data type for each attribute: (a) the price of a ticket, (b) whether a playlist is downloaded, (c) the name of a candidate, (d) the date of birth of a student.
[4]State: Give a short answer, no explanation needed.
2.Explain why a telephone number is usually stored as text, not as an integer.
[3]Explain: Give reasons: say what happens AND why, or how one thing causes another.
3.Explain two problems that could arise if prices are stored as text.
[3]Explain: Give reasons: say what happens AND why, or how one thing causes another.
4.Distinguish between the CHAR and VARCHAR data types.
[2]Distinguish: Make the differences between two things clear.
In a nutshell
- Every attribute has a data type, which says what kind of value it holds, how much space it uses and which operations are allowed.
- Text: CHAR(n) fixed length, VARCHAR(n) variable length up to n, TEXT for long text. Numbers: INT for whole numbers, REAL or DECIMAL for numbers with a fraction. Others: DATE, TIME, BOOLEAN.
- The right type lets you calculate (numbers), sort and compare correctly (dates) and rejects nonsense values.
- Identifiers such as phone numbers and postcodes are stored as text, because you never do sums on them and they can start with zero.
- Use the same type for the same attribute everywhere (database and program), or comparisons and inserts can fail.