Relational databases: data type

The idea

In a relational database, every field (column) is given a data type that states what kind of value it may hold and how that value is stored and compared. Common types include text (short strings such as a name), integer (whole numbers such as a quantity), real or decimal (numbers with a fractional part such as a price), date/time (calendar values such as a booking date), Boolean (true/false flags such as whether a book is on loan) and currency. Choosing the correct type keeps data consistent, allows sensible validation and sorting, and lets queries compare values correctly. For example, storing a price as text would make numeric totals and range checks unreliable, whereas a real or currency type supports arithmetic. A candidate question asks you to identify which data type suits a given field, so read the field's purpose and the sample values before choosing.

What earns the marks

• A data type defines the kind of value a field may store, such as text, integer, real, date/time or Boolean.
• Choosing an appropriate data type supports validation and consistent storage of values in a field.
• Numeric types such as integer or real allow arithmetic and range comparisons; text does not.
• A Boolean type stores true/false values, suitable for flags such as on loan or available.
• A date/time type stores calendar values so they can be sorted and compared chronologically.
• Matching the data type to the field's purpose prevents invalid or inconsistent entries.

Common mistakes and how to put them right

• Assuming any field can hold any value.

  Put it right: Each field has a declared data type that restricts the values it accepts.

• Treating a price as text.

  Put it right: A numeric type such as real or currency is needed for arithmetic and range checks.

• Confusing a data type with a primary key.

  Put it right: A data type describes the kind of value; a primary key is a field that uniquely identifies a record.

Exam tips

• Read the field name and any sample values before selecting a data type.
• Eliminate options that cannot support the required operation, such as arithmetic on a text field.
• Check whether the value is whole, fractional, a date or a true/false flag before deciding.