Validation
An advantage of electronic databases are the validation checks available:
- presence
- restricted choice
- field length
- range
Presence
A presence check makes the person using the database enter something in this field. They cannot leave it blank. For example, when signing up for a new account on a social media platform, you cannot leave the password field empty.
Restricted choice
Restricted choice cuts down on mistakes by only letting you select an option from a menu or list.
Field length
Field length restricts the number of characters typed. This can cut down on the total data requirements of the table.
A typical length check could be used on a National Insurance number field to only allow up to nine characters. This can also stop people from mistyping their NI number by adding in too many characters.
Range
A range check makes sure data entered is within certain limits. This could be used to make sure the user enters a number ˃=1 and ˂=12 if they were entering their month of birth, or that they enter a number ˃=11 and ˂=16 for the database of school pupils between these ages, as shown in the image below.
Sports centre example
The data dictionary for the sports centre tables is shown below. Some of the attributes in each table make use of validation.
Entity: Surface
| Attribute | Key | Type | Size | Required | Validation |
| Surface ID | PK | Text | 4 | Yes | Length = 4 |
| Surface Type | Text | ||||
| Supplier | Text | Restricted Choice: Surface4U, We Lay, Council | |||
| All Weather | Boolean | ||||
| Cost per m2 | Number |
| Attribute | Surface ID |
|---|---|
| Key | PK |
| Type | Text |
| Size | 4 |
| Required | Yes |
| Validation | Length = 4 |
| Attribute | Surface Type |
|---|---|
| Key | |
| Type | Text |
| Size | |
| Required | |
| Validation |
| Attribute | Supplier |
|---|---|
| Key | |
| Type | Text |
| Size | |
| Required | |
| Validation | Restricted Choice: Surface4U, We Lay, Council |
| Attribute | All Weather |
|---|---|
| Key | |
| Type | Boolean |
| Size | |
| Required | |
| Validation |
| Attribute | Cost per m2 |
|---|---|
| Key | |
| Type | Number |
| Size | |
| Required | |
| Validation |
Entity: Sports area
| Attribute | Key | Type | Size | Required | Validation |
| Area ID | PK | Text | 3 | Yes | Length = 3 |
| Area name | Text | 20 | |||
| Surface ID | FK | Text | 4 | Yes | Length = 4, linked to SurfaceID in Type of Surface entity |
| Hire Cost | Number | Range >=2 and <=80 | |||
| Manager | Text | ||||
| Available | Boolean |
| Attribute | Area ID |
|---|---|
| Key | PK |
| Type | Text |
| Size | 3 |
| Required | Yes |
| Validation | Length = 3 |
| Attribute | Area name |
|---|---|
| Key | |
| Type | Text |
| Size | 20 |
| Required | |
| Validation |
| Attribute | Surface ID |
|---|---|
| Key | FK |
| Type | Text |
| Size | 4 |
| Required | Yes |
| Validation | Length = 4, linked to SurfaceID in Type of Surface entity |
| Attribute | Hire Cost |
|---|---|
| Key | |
| Type | Number |
| Size | |
| Required | |
| Validation | Range >=2 and <=80 |
| Attribute | Manager |
|---|---|
| Key | |
| Type | Text |
| Size | |
| Required | |
| Validation |
| Attribute | Available |
|---|---|
| Key | |
| Type | Boolean |
| Size | |
| Required | |
| Validation |
The required column of a data dictionary is used to indicate the need for presence check validation. If the word ‘Yes’ appears in this column, the developer will know that they need to include a presence check when creating the table in a database package.
