Database Validation

It is imperative that data is entered correctly in a database.  An automated database has the benefit of including automatic checks in order to prevent incorrect data from being recorded into the database.  This is a process which isn’t available in a manual database.

Validation is a process whereby the data entered in the database is checked to make sure that it is sensible.  For example, validation can be utilized to check that only Male or Female is entered in a sex field.  It cannot check whether or not the data entered is correct. It can only check whether or not the data makes sense.

Validation is a way of trying to lessen the number of errors during the process of data input.

Validation is carried out by the computer when you input data.  It is a way of checking the input data against the set of validation rules.

The purpose of validation is to make sure that data is a) logical, b) rational, and c) complete and within acceptable limits.

Database Validation Methods

Type – If you make a specific field numeric then it won’t allow you to input any letters or other non-numeric characters.  Be wary when using the numeric data type. If you use it for fields like phone numbers, it won’t allow you to enter spaces, or other human-friendly forms of formatting.

Some data types can carry out an extra type check.  For example, a date data type will ensure that a date inputted existed at some point, or could exist in the future.  It would not accept the date 30/02/2018.

Presence – This is sometimes called Allow Blank or Mandatory.  This type of validation compels the user to enter data in the required field.

For example, in an address book, you can make either the address or phone number optional, while you must make the name field required.  Leaving a mandatory field blank will trigger an error message that will prevent you from proceeding to the next step.

Unique Identifier – It is essential that one record can be plainly recognised from another record. Generally speaking, each record has one field that functions as a unique identifier for a record.  An easy validation check can be done to make sure that a value occurs only once in this field—it doesn’t matter if there are thousands of records in the database, the check can be carried out just the same.

Range Check – Range check is a validation check which can be applied to numeric fields. This is done to ensure that only numbers within a certain domain can be entered into a field.  Remember that this does not necessarily mean that the data entered will be correct.  But it will certainly lie within reasonable limits.

Format – This is used for a field that requires an entry in a specific format.  Examples include date format, postal codes, and driver’s license numbers.

Restricted Choice – There are times that fields in a database have a definite amount of data that can be entered into them. For example, the amount of days in a week are limited to Sunday, Monday, Tuesday, etc.

Programming a database to accept only one of a series of valid choices can prevent errors, and can also serve to lessen the time it takes to input data.

This has different forms like a list box, combo box, or radio button.

Benefits of Restricted Choice:

• Faster data entry, because it is typically much quicker to select from a list than to type each individual entry.
• Enhanced accuracy, because it lessens the risk of spelling mistakes.
• Limits the options to choose from by only displaying the essential choices.

Referential Integrity – If you’re using a relational database, then you can impose referential integrity to validate inputs.  You can check data inputs in certain fields against values in other tables.  For example, in the job database, when a new hire is entered, you could check the supervisor name against the employee table, just like you could check the department name against the department table.

Further Readings:

• Data validation