Database Concepts
Databases
When a table is created, each field is given a that says what kind of value it can hold. Choosing the right type makes the data smaller, faster and safer.
| Data type | Holds | Example fields |
|---|---|---|
| Integer | Whole numbers | Age, year of birth, ID number, quantity in stock |
| Real | Numbers with a fractional part | Price, weight, percentage with decimals |
| Text / Alphanumeric | A sequence of characters | Name, address, description |
| Character | A single character | A grade letter ('A'), a size code ('S', 'M' or 'L') |
| Date / Time | A specific calendar date or time | Date of birth, date joined, last-login timestamp |
| Boolean | A simple true/false value | "Has paid?" "Is active?" "Newsletter subscriber?" |
Choosing the right type
For each field, ask three short questions in order:
- Does it ever need decimals? Yes →
Real. No → keep going. - Is it a count or whole-number ID? Yes →
Integer(an ID that contains letters, such asAB123, is text). No → keep going. - Is it just true or false? Yes →
Boolean. Is it a date or timestamp? Yes →Date/Time. Is it exactly one character (a code or flag)? Yes →Character. Otherwise →Text / Alphanumeric.
Two common slips to avoid. Phone numbers and postcodes are stored as text, not integers: they may start with a leading zero, contain spaces or letters, and never have arithmetic done on them. Calendar dates are stored as a Date type, not as text: only a true date field can sort chronologically and reject impossible values like 2024-02-30.
Common exam question
Choosing the data type for each field
Question: Complete a table giving the most suitable data type for each field, chosen from a list of types (2 marks).
Asked in 5 of the 17 papers. With four fields, all four right earns both marks and two or three right earns one. Decide from the example data:
- a code mixing letters and digits, a name or a description: text/alphanumeric
- only ever one of two values, such as Yes/No or T/F: Boolean (one scheme also took Boolean for a gender field holding M or F)
- whole numbers: integer, even for money (a weekly rate in whole dollars was marked integer)
- values with decimal places, such as a price of 12.95: real
- a date: date/time
One paper turns it round and asks for a different field to fit each type. Another asks for a name and a type for each described item, one mark per pair; write the name as one word, such as DateJoined.
Why the right data type matters
- Memory efficiency: an integer takes less space than the same number stored as text.
- Correct comparisons:
>and<compare numbers by value but text character by character, so as text"10"comes before"9". - Validation: a date field rejects a value like "purple" automatically.
- Sorting: a properly-typed date field sorts chronologically; the same date as text would sort alphabetically.
Quick check
4 short questions on this note. See what has stuck, and where to look if something hasn't.