Databases and data capture

Before setting up a database, the record structure must be decided to make best use of the memory and backing store, and to make searching and report creation easier.

For example, a car showroom wants to record details of the cars it sells. Before setting up the database, the following questions need to be answered:

  1. What information is needed?
  2. What validation could there be?

With these questions answered, informed decisions can be made about the record structure. This is how it might begin:

Field name Field type Format
Registration number Alphanumeric Up to 7 characters - the key field
Make Alphanumeric Up to 15 characters
Model Alphanumeric Up to 15 characters
Date first registered Date DDMMYY
Price Currency Up to 5 numbers
Taxed Yes/No (Boolean) 1 character Y/N

When designing a database it is important to choose the correct field type. This ensures that the data stored is usable and it makes validation easier. For example, if the price paid for goods was stored in a text field, then the database wouldn’t be able to add each individual figure to produce a total.

Key fields

A database should always contain a key field.

The key field is a unique identifier for each record.

The following are examples of key fields:

  • car registration number
  • National Insurance number
  • your school's examination centre number
  • your own examination candidate number

Storing data in tables

Databases store data in tables, a single database file can store many tables, queries and reports. In the example table below there are six columns (divided vertically) and four rows (divided horizontally), each column has a heading, eg Registration number.

Reg. number Make Model Date registered Price Taxed
R623 PHM Ford Fiesta 010198 6800 Y
P887 LHW Rover 200 010397 7500 Y
P812 WHJ Peugeot 406 010996 7000 N

A database in which all the data is stored in a single table is known as a flat file database.

Car showroom database onscreen with forecourt visible through the window
  1. application A software program that allows a user to perform a specific task.
  2. backing store A computer's primary data store, ie the hard disk.
  3. data Values, typically letters or numbers.
  4. database A data store designed in an organised way, making it easier to search for the information you need.
  5. information Data that has meaning, not just a number or a letter.
  6. key field A unique identifier for a database record or table entry.
  7. memory The part of a computer that stores data.
  8. multi-access A system that can be used by several users simultaneously via a local area network (LAN).
  9. query A search or question performed inside a database.
  10. validation Checking input data is sensible and in the right format.