Chronic data quality problems?
- Line managers fail to advise HR of changes e.g. employee surname, change of address, marital status, promotion or transfer, left the company, etc – expecting that HR should somehow mind-read what happens in the lives of hundreds or thousands of employees
- Line managers advise HR of changes, but the system is not updated to reflect the changes
- Maybe you’re new into the situation, and finding that there’s a legacy of data mismanagement that you have to deal with
- The HR data cleanses that happen regularly were only partially accurate, and there are still many data items out of date
- Many of the current employees were TUPE’d in but the new data was either incorrectly or not fully captured
“How come our HR data is such a mess?” – Chief People Officer
Think a new HR system would solve your problems?
You’ll need to understand a couple of terms –
“…and nothing but the Key”
The following conditions have to be satisfied for us to say that the database is in 3NF –
- It is in second normal form
- There is no transitive functional dependency
What do we mean by transitive functional dependency? It means we have the following relationships in the table: A is functionally dependent on B, and B is functionally dependent on C. In this case, C is transitively dependent on A via B. The columns depend on primary key and nothing else. Hence “The Key, the Whole Key, and Nothing but the Key”.
Consider another example –
|3075||2||FT Fixed term||16|
|3077||4||PT Fixed Term||8|
In the above table, Job_ID determines Contract_ID and Contract_ID determines Contract_Type. Therefore Job_Id determines Contract_Type and we have transitive functional dependency, which means that this structure doesn’t satisfy 3NF.
To bring this table to third normal form, we split the table into two as follows:
|3||FT Fixed Term|
|4||PT Fixed Term|
Now all the non-key attributes are fully functional dependent only on the primary key. In the table JOB_DETAILS, both the Job_ID and Salary_Band are only dependent on Job_ID. In the table SALARY_BAND, Contract_Type is only dependent on Contract_ID.
OK, but I work in HR and you’re bamboozling me with tech stuff…
Not really. That’s kind of a weird attitude born of techno-fear. There’s a real benefit in learning about database normalisation and SQL though.Why be dependent on your IT team to grasp what data you need to add value in your job? Even if you aren’t working with data on a daily basis, learning database normalisation and SQL will probably help you to become a more systematic thinker. As with any programming language, you have to be logical and structured, and you have to develop solutions to problems. Knowing this stuff can also help you in areas like project management, procedure development and communications. Database normalisation and SQL are much easier for the average person with no coding knowledge – basically it’s simpler than programming languages, and most people have a basic grasp of how tables work.