A phone number with seven digits
Greenfield Youth Club (made up) is moving its members from an old list to a new system. One phone number has only seven digits. One town is spelt Rivrside. One join date is the 40th of the 13th month.
If those go into the new system, the mistakes move with them. The new system must stop them at the door.
By the end of this lesson
Note
I can set rules that keep data valid and check moved data against a master list.
Key words
| Word | What it means | Example |
|---|---|---|
| Input mask | a pattern that each typed character must match | 07 then 9 digits |
| Default value | the value a field gets if nobody types one | |
| Validation rule | a rule that refuses data that is not allowed | |
| Master data | the one agreed list that other data is checked against | |
| Data migration | moving data from one system to another | |
| Quality control | checking data after it is entered to find errors |
The cells students fill in are left blank in this preview.
Starter: what you already know
Have a go
Starter: what you already know
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Four controls stop bad data at entry
The best fix is to stop bad data going in. Four controls do this.
An input mask forces a pattern, such as 11 digits starting 07. A default value fills a field when nobody types. A validation rule refuses values that break a rule, such as a range. A list allows only values from a short list, such as Active, Paused or Left.
The four controls
| Control | What it does | Example |
|---|---|---|
| Input mask | Forces a pattern | Phone: 07 then nine digits |
| Default value | Fills in when empty | Status starts as Active |
| Validation rule | Refuses wrong values | Age between 5 and 100 |
| List | Allows only listed values | Status: Active, Paused or Left |
The cells students fill in are left blank in this preview.
Try it: check the old list
-- Mask check: phones that are not 07 plus nine digits.
SELECT member_ref, phone FROM old_members
WHERE phone NOT GLOB '07[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
-- Master list check: towns that are not on the master list.
SELECT member_ref, town FROM old_members
WHERE LOWER(TRIM(town)) NOT IN (SELECT LOWER(town) FROM master_towns);
-- Date check: dates that are not real dates.
SELECT member_ref, joined FROM old_members WHERE DATE(joined) IS NULL;
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.