Sometimes it's good to go back to the basics for a refresher lesson. Last year I did some training with analysts and realized not everyone who works with data knows what data normalization actually means. Most have heard of it but not all understand it.
So I'm going to take a few posts and go back to the basics.
Data Normalization
The concept of data normalization was first described by Edgar F. Codd in 1971 while he was working at IBM. He is credited with creating the theoretical basis for relational databases.
The purpose of normalized data structures included:
minimizing data storage
reducing data write times
ensuring data consistency
ensuring data queries would return meaningful results
The first 3 bullets can be accomplished by ensuring each data element is only stored once. The fourth bullet requires some additional structure. All four purposes were accomplished by the normalization rules that E.F. Codd developed.
First Normal Form
There are different definitions of the first normal form (1NF) on the internet, but I find it easiest to think of it in these terms:
Each field has only one value
Each row has a unique key
The dataset below shows the winners of the company's Employee of the Year award for the past few years. It violates 1NF because it contains two year values in the first row. It appears that George won the award in 2010 and again in 2014, so that information has been stored in a single record.
By separating out the 2010 and 2014 information into distinct rows, this dataset becomes compliant with 1NF. The Year column provides a unique key for each row.
However, let's take the dataset back one year when there was an unusual situation. Two people, Sam and Maria, shared the award in 2009. Once again our dataset violates 1NF because two fields have multiple values, Winner and Department.
We solve the problem just like we did before by separating out the 2009 record into two rows, one for each award recipient. However, now the Year is no longer a unique key. The year 2009 shows up twice, so we need to add another field to uniquely identify each row. Winner works nicely, so now Year and Winner together become the unique key. Whenever there is more than one field in a key, it is called a Compound Key, and that leads nicely into the next article on Second Normal Form (2NF).
I saw a fascinating article this week by Karl Smallwood on the website www.TodayIFoundOut.com. Apparently for 20 years, the Minuteman nuclear silos all had the same launch code of 00000000. Truth really is stranger than fiction.
It reminds me of the Spaceballs scene where the king is blackmailed into revealing his secret combination of 12345. Rick Moranis' character shouts back, "That's the stupidest combination I've ever heard in my life! That's the kind of thing an idiot would have on his luggage!" Apparently the luggage of idiots was about as secure as the American nuclear arsenal for many years.
The launch code was revealed in 2004 by a former Minuteman launch officer. Apparently this passcode security was initiated by JFK to prevent a rogue general from starting a nuclear war on his own. The passcode was accompanied by a strong security system that could not be hot-wired to get around the passcode -- the missile simply could not launch without it. However, that entire safety system was undermined by generals who ignored the president's order and required the passcodes to be reset to zeroes after each inspection to ensure the missiles could be launched quickly if the Russians ever fired first.
I experienced a similar situation at one large organization that had very tight security on their enterprise data warehouse, although unlike the generals in charge of missiles this was an unintentional breach of security policy. Because a portion of the data warehouse contained sensitive client information, separate passwords were issued only to those employees that had a justifiable business need to access the data. The complex password could not be changed by the user and it expired after a few months so the user had to repeat the application process to prove that they still had a business need to access the data. It was a fairly complex security system that was completely undermined by a single failed assumption. A new security analyst was transferred from the helpdesk to the data warehouse team to issue passwords, and he thought it was the same process as at the helpdesk, namely issuing temporary passwords to users who had forgotten theirs. No manager got around to telling him what his new role actually entailed. When I got my data warehouse password, I was shocked to see it was abc123. I soon found out that everyone in the department had the same password and that this had been happening for a few weeks already. Because no user liked the old complex passwords that they couldn't remember, everyone was thrilled with this new simple password and no one had any incentive to notify the data security manager of the obvious flaw in the process. Despite very strict data security processes and policies, sensitive client data had been made as secure as an idiot's luggage.
What's the lesson?
It is so easy to make assumptions that will undermine the greatest of plans. Everyone assumed that generals would always obey an order from the president. That turned out to be a false assumption. Everyone assumed that each new data security analyst would be trained in the process of issuing new data warehouse passwords. On one occasion that turned out to be a false assumption. Simple human failures can undermine even the most complex security processes when assumptions are not identified and tested.
I've designed data processes for many years. It's a helpful discipline to get in the habit of asking myself:
What assumptions have I made this time?
If any of those assumptions turn out to be false, will that have a significant effect on my process?
If the effect is significant, can I build control processes to identify when those assumptions are false?
For example, in the data warehouse password situation above, the assumption was that the person issuing passwords will be trained on the procedures for issuing passwords. If that assumption is false, the effect is very significant. Therefore a simple control process would be to generate a report each day showing all the new passwords that have been issued. One glance at such a report would've shown that all passwords were suddenly identical and that an urgent problem existed.
The challenge is that answering question #1 is incredibly difficult. We simply aren't used to identifying our assumptions -- they're automatic. One way to get around that is to skip question #1 entirely and rephrase question #2 to be:
2. If my process fails, will that have a significant effect on the business?
If yes, then build some control processes to identify process failures as early as possible. The failed assumptions will become clear later once problems are encountered.
