GoalChoose a data type and a sensible field length for each field, and record the choices in a data dictionary.
New words
data type · the kind of data a field can store, such as text, number or datefield length · the largest number of characters a text field may holddata dictionary · a table that lists each field’s name, data type and length when a database is designedcurrency · a data type for amounts of money
Explanation
Every field must have a data type, because the type decides what the computer can do with the data. Text holds letters, digits and symbols that are not used in calculations. A phone number is Text, because you never add phone numbers and a number would lose its first zero. Number holds values you may calculate with, Currency holds money, Date/Time holds dates and Yes/No holds only two values. Autonumber counts up by itself. Text fields also get a field length: a database program usually sets aside 255 characters, so designers choose a shorter length to save space. Designers list all their choices in a data dictionary before they build the table.
Field name
Data type
Field length
Key
AnimalID
Autonumber
–
*
Name
Text
20
Species
Text
15
Age
Number (whole)
–
DateArrived
Date/Time
–
Fee
Currency
–
Vaccinated
Yes/No
–
Worked examples
Sample value
Best data type
Why
0555 0123
Text
a phone number; no calculations; keeps the first 0
11
Number
can be added or compared
12/03/2025
Date/Time
a date
25.50
Currency
an amount of money
Yes
Yes/No
only two possible answers
The animal shelter’s data dictionary above has one line for each field. The star shows the primary key, and a length is only needed for text fields. This table shows how sample values are matched to data types.
Field
Longest value expected
Characters
Length chosen
Name
Bartholomew
11
20
Species
chimpanzee
10
15
Code
ANI-0042
8
10
Choose the length by thinking about the longest value that might be typed, and then add some room. Names longer than 20 letters are rare, so 20 is a sensible length for a pet’s name. A length of 255 would waste space in a big table with thousands of records.
Age as Number, sorted
Age as Text, sorted
2
10
5
2
9
5
10
9
The data type also decides what you can do. You can ask the database for all animals older than 5 only if Age is stored as a number. If Age were Text, 10 would be sorted before 9, because text is sorted letter by letter and the character 1 comes before 9.
Class activity
My collection dictionary (no computer). Choose something you or your family collect or keep a list of, such as books, stamps, plants or football cards. On paper write a data dictionary with at least six fields: a field name, a data type and a field length for the text fields. Add a star to the primary key. Then write three sample records and check each value: does it fit the type? Does the text fit in the length? Swap with a partner, who must find one field that could be improved and say why.
Practice
1
Here is one record of a seed table. Choose the best data type for each field.
A designer chose Number for a phone-number field. Ali types 0555 0123 and the database stores 5550123. What went wrong, and which data type should be used instead?
A number does not keep a leading zero, so the first 0 was lost. A phone number is never used in a calculation, so it should be Text.
3
This data dictionary has some poor choices. Find three of them and say how to fix each one.
Field name
Data type
Field length
Name
Text
255
Age
Text
3
Cost
Number
–
Joined
Text
10
Name has length 255, which wastes space: use about 20. Age is Text, so it cannot be compared properly: use Number. Cost is Number but is money: use Currency. (Joined as Text is also poor: use Date/Time.)
4
On paper write a data dictionary for a table that records the trips of a school bus. Use at least five fields, mark the primary key with a star and give a length for each text field.
Model answer: TripID (Autonumber, *), Route (Text, 20), Driver (Text, 30), Date (Date/Time), Pupils (Number), LateArrival (Yes/No). Any sensible fields with matching types are correct. The key must be unique.