A field type tells a database what kind of value a field may hold: text, whole number, decimal, currency, date or yes/no. You choose it by looking at sample values and asking what will be done with them. This skill appears whenever a practical task gives you a table to build or a data file to import.
The type you pick controls what the database accepts, how values are sorted, and whether calculations work. A wrong choice rarely shows an error at once.
It shows up later as a sort in the wrong order or a missing zero at the start. This page sits in database structure and validation.
How do sample values point to a type?
Read three or four real values from the column, then work through these questions in order.
- Is it a true or false answer? If every value is one of two states, use a Boolean (yes/no) field.
- Is it a date or time? Use a date field, so sorting follows the calendar and not the alphabet.
- Will you calculate with it? Quantities and counts suit an integer field. Amounts with decimals suit a decimal or currency field.
- Is it a label, even if it is made of digits? Use text. Codes, phone numbers, postcodes and identity numbers are labels.
A good habit is to scan for a zero at the start, a hyphen, a letter or a space. Any of these is a strong sign the field is text.
Worked example
A fictional school bookshop (invented for this lesson) keeps stock records. Here are the sample rows:
| StockCode | Title | Price | Quantity | DateReceived | Fragile |
|---|---|---|---|---|---|
| BK-045 | Science Notebook | 8.90 | 120 | 2025-02-03 | No |
| BK-046 | Atlas Pocket Edition | 24.50 | 35 | 2025-02-03 | Yes |
| ST-007 | Gel Pen Set | 6.00 | 300 | 2025-02-10 | No |
StockCode: values such as BK-045 contain letters and a hyphen, and nobody adds codes together. Use text.
Title: words only. Use text.
Price: values like 8.90 have two decimal places and will be multiplied by quantities. Use currency or decimal.
Quantity: whole items, 120 and 35 and 300, which are counted and totalled. Use integer.
DateReceived: each value is a calendar date written year-month-day. Use date.
Fragile: only Yes or No appears. Use Boolean (yes/no).
Quick check on the Price column: if the three prices are summed, 8.90 + 24.50 + 6.00 = 39.40. A currency or decimal field can do that sum directly. Verify it again: 8.90 + 24.50 = 33.40, and 33.40 + 6.00 = 39.40.
The mistake to watch for
A frequent slip is to choose a number type for a phone number because it looks like digits.
Mistaken choice: Phone = number. The value an invented number such as 01X-XXXXXXX is entered.
A number field cannot hold the hyphen, and if it is typed without one, the zero at the start is dropped. a number starting with a zero can lose it and become a shorter, different value, which is a different value and cannot be dialled.
The correction is to ask “will I add or average these?” Phone numbers fail that test, so the field must be text. The same reasoning covers postcodes, membership codes and ID numbers.
Check yourself
Decide the field type for each column and give a reason.
1. A column of postcodes such as 08000 and 50450.
Show answer
Text. A postcode is a label and not a quantity. The zero at the start of 08000 would be lost in a number field.
2. A column called AmountPaid with values 45.50, 30.00 and 12.75.
Show answer
Currency or decimal. The values have decimal places and will be added to give a total.
3. A column called Returned with the values Yes, No, No, Yes.
Show answer
Boolean (yes/no). There are only two possible states, so the database can reject any other entry.
Where this leads next
Once you can justify a type from sample values, move on to defining a key and a validation condition, then test the whole module with the database structure and validation practice set. The read-only SQL practice lab and the ICT practical task and evidence checker let you see how field types behave on invented data.
Some students choose the right type in familiar tables but second-guess themselves when the data looks unusual. A teacher in online one-to-one ICT tuition can set new tables and ask you to explain each choice aloud.