Validation

Validation is the name given to the process whereby the information entered in the database is checked to ensure that it makes sense. For example, you can use validation to check that only numbers between 0 and 100 are entered in a percentage field, or only Male or Female is entered in a sex field. It cannot check that it is correct (the process of checking that the data is actually right is called verification).

For a somewhat surreal demonstration of the difference between valid data and data that is correct (i.e. has been verified), check my Words of Wisdom page - it generates valid sentences, but some of them can't be verified!

Obviously it's very important that the information in your database is correct if you're going to get worthwhile results when you search or sort the data. There are various methods that you can use to check your data.

Type

The use of field types (see the Structure page) forms a basic type of validation. If you make a particular field numeric (i.e. a number), for example, then it won't let you enter any letters or other non-numeric characters. Be careful when using the numeric types, however - if you use them for things like phone numbers, for example, you won't be able to enter spaces or any other sorts of formatting.

Presence

This type of validation might go by different names, depending on your database program - sometimes it's called something like Allow Blank or mandatory for example. This type of validation forces the user to enter the data in that field.

If you had an address book, for example, you might know the person's address and not their phone number, or vice-versa, so it wouldn't make sense to make those fields mandatory. On the other hand, it doesn't make sense to have an address book entry with no name, so you should check for the presence of the name.

Uniqueness

Some database programs allow you to check whether the contents of a particular field are unique. This might be useful to prevent users entering the same information twice. For example, if you were creating a car database, you should make the registration number field unique as no two cars should have the same number.

Range

If you're using a number field, then you might want to limit the range of inputs. For example, you might want to limit prices in a stock database so that they are all positive, or limit the range of a percentage field so that the values entered are between 0 and 100.

Format

You might have a field in your database that requires an entry in a particular format. A simple example might be a date, or piece of text of a certain length. More complex examples might include things like postcodes, or National Insurance or driving licence numbers. If you're using Access, you can define your own formats using an input mask, which defines the valid characters.

Multiple Choice

A good way to validate fields is to use multiple choice responses. These might take the form of a listbox, combo box, or radio button. For example, you could create a field that would only allow the user to select from Yes or No, or Male or Female. This can be an especially useful techniques in database applications such as Access, which allow you to dynamically generate the choices.

For example, if you created a database system to manage bookings, rather than checking the dates and times after they have been entered, to check that there are no double-bookings, you could use a query and a combo-box to only show the available times. That would stop you making double-bookings in the first place, and make any subsequent validation much simpler.

Referential Integrity

Finally, if you're using a relational database, then you can enforce referential integrity to validate inputs. This means you can check entries in certain fields against values in other tables. For example, in the merits database, when a new merit is entered, you could check the names of the students and teachers against the student and staff tables, to prevent either spelling errors, or the entry of merits for students that don't exist.