Unique Keys - The Primary Cause of Duplication in Databases!

The Data Quality Paradox

The mistaken use of unique keys as the unique identifiers of records is perhaps the greatest paradox of data quality and the primary cause of duplication of entries in any database.

The problem starts when data analysts use codes as the Unique Identifiers (UIDs) of data entities in the data modelling stage of analysis.

Data Modeling 101: Codes are not identifiers!

Data Modelling 102: The unique identifier of a Data Entity is NEVER A CODE!!

Many data analysts may have apoplexy at these statements, as it is a practice that they have followed most of their modelling lives, but simple logic can show the practice to be totally flawed.

For something to be a unique identifier it must first of all qualify as an identifier. Codes do not identify! Don't believe me?

A Simple Test

If I were to take you to the middle of a warehouse with 400 unlabeled parts laid out on the floor and ask you to go and pick up part 14563 you would not be able to proceed. No. Why? Because 14563 does not in any way identify any of the parts. If I say "pick up a motor" then you will be able to make a start. You might need to ask "do you want a hydraulic motor or an electric motor?" But you will be able to identify a motor among the parts.

We see from this that codes are not identifiers. If they are not identifiers then they CANNOT be unique identifiers.

What is a UID?

A unique identifier enables us to answer the question; "what is it, with respect to this enterprise, that makes one occurrence of a data entity uniquely different from every other occurrence of that data entity?" If we know this then we will have the unique identifier for that data entity.

With regard to a finance company that loans money for the purchase of motor vehicles, lets ask the following question. "With regard to this enterprise, what is it that makes one customer uniquely different from another customer?" For the purposes of this example we will stick to customers who are individuals, as opposed to corporate entities.

Straight away, some data analysts will shout out "it's the customer number!" Wrong!

Example UID Problem

To demonstrate this I could take you to many finance companies and show you the customer data files with records that would look very similar to the following.

Cust No Forename Surname Street Town D.O.B

1001 John Smith 123 High St Bristol 22 Dec 1950

1002 John Smith 123 High St Bristol 22 Dec 1950

1003 John Smith 123 High St Bristol 20 Aug 1971

1004 Mary Jones 27 West St Walsall 12 May 1975

1005 Mary Green 27 West St Walsall 12 May 1975

1006 Mary Jones 22 Grove St London 12 May 1975

Every customer has a different Customer Number, but do these six records represent six distinct customers?

It is highly likely that customer No 1001 and 1002 are the same person. But what about 1003? The same person again with an error in the date of birth or a younger person of the same name at that address; the son of the older John Smith perhaps?

Looking at Customer Numbers 1004 and 1005, we ask is Mary Green the same person as Mary Jones - now married to a Mr Green perhaps? Or is she an entirely different person who just happens to have the same date of birth?

What about Customer 1006? Is this the same Mary Jones who has moved house? If she had a bad debt record at 27 West St would you want to do business with her at 22 Grove St?

Data Uniqueness is a Business Decision

This set of records forces us to repeat the question; "What is it, in the context of this enterprise, that makes one customer uniquely different from every other customer?"

One thing that is for certain is that it is NOT going to be the Customer Number!

Uniqueness must be defined by the enterprise. It is the job of data analysts to help the enterprise arrive at this definition.

Database designers have the job to implement this uniqueness in a manner that prevents duplicate records being created in the database.

It is impossible for database designers to implement uniqueness if the enterprise has not defined uniqueness from a business point of view and the data analysts have not modelled this.

Unique Keys are Not UIDs

The use of the word "unique" in the term "unique key" and in "unique identifier" at some point in the past got misinterpreted completely by data analysts and database designers, in that they thought that unique keys were a mechanism for implementing unique identity in a database.

Because of this, what they did was to introduce to every database a mechanism that would, above all else, allow duplication to occur!

Amazingly, this error is still practiced worldwide on a daily basis, and not just by those analysts and designers who made the mistake many years ago but also, sadly, by those coming new to data analysis and database design.

Let's Stop it Now!

Removing Duplicate Records

How can all of these duplicate records be removed from your database? You could buy a clever piece of software that will do it for you?

You could, but it would simply be changing the problem not solving it. You would either end up with a database with no duplicates removed or with a whole lot of records merged that should never have been merged. Try to sort that one out!

The fact is that, if the enterprise has not defined what it sees as the elements that make a data entity unique, then no piece of software can.

Notes

The data shown above is only a short, simplified and denormalised example, but is typical of data held by various finance companies for whom I have done consultancy.

I am not making an argument against unique primary keys in tables, as these are essential, but against confusing unique keys with unique identifiers.

Get a full description of how to model UIDs for data entities by picking up the Integrated Modelling Method Data Modelling eBook. A full set of Free Rapid Guides also available.

Author: John Owens
Email: john@integrated-modeling-method.com
Blog: http://www.johnowensblog.com
Skype: johnowensnz
Phone: +64 (0)21 774 785

This post was made using the Auto Blogging Software from WebMagnates.org This line will not appear when posts are made after activating the software to full version.

READ MORE - Unique Keys - The Primary Cause of Duplication in Databases!

It seems the older we get the more responsibilities we have and the less time we have to see to them. There are so many things that go on in day to day life it's no wonder that people get overwhelmed and stressed out. Work is a primary concern for many people. This is because most people must work in order to provide a life for themselves and their families.

Additionally, many employers do not respect the idea of "life-work balance". This concept extols the virtues of having a healthy balance between personal life and work. Personal life comprises anything that defines who you are and what you believe in. Personal life includes your family, friends, hobbies and religious beliefs. There should be a good balance between your involvement with these components of your life and your work life. Too much work can be a definite stress. Conversely, not enough work and stress builds because of financial concerns. Most people have accepted the fact that stress is a natural part of life and unfortunately it cannot be eliminated completely. However, stress can be managed successfully with regular exercise, taking the time to laugh at life and a healthy diet.

Regular exercise is one of the best things that a person can do for themselves. Studies show that people who exercise regularly are less likely to have a heart attack or stroke. They also are at less risk of developing hypertension and diabetes. Additionally, people who exercise are rarely overweight, have fewer joint problems and hardly any flexibility issues. Because exercise releases certain bodily chemicals known as endorphins, exercise is a natural stress reliever.

Taking the time to laugh at life is another natural stress reliever. Just like when you exercise, when you laugh your brain releases endorphins. These endorphins help to promote a sense of well-being. Try to see the humor in life on a daily basis. This can be accomplished by adding funny stickers to your surroundings. Placing funny stickers around your home, car and workplace or anywhere appropriate will help remind you to laugh. Funny stickers can be used on several different locations throughout your home and office. For instance, if you find a funny sticker that really puts a smile on your face, stick it in your desk drawer at work. Then whenever, you are having a bad day at work, you need only to open your desk drawer to bring a smile to your face.

Finally, research shows that eating a healthy diet can help to alleviate stress triggers. As we age, our bodies do not process fatty, high calorie foods the way that they did in our younger years. This is due in part to the fact that as we get older, our metabolisms slow down on a continuous basis. It is important to choose foods high in fiber and low in fat and calories to ensure that you will feel your best. Another benefit of eating a healthy diet is that people who eat well also have lower cholesterol. High cholesterol has been proven to increase the chances of developing heart disease.

By taking just a few small steps, you can effectively lower and manage your stress levels. It is important to remember stress will not go away on its own. If you ignore it the problem will only get worse. If you feel that you are under a large amount of stress, take proactive measures now to manage it before the stress gets worse. Stress can lead to a multitude of medicals issues, but you can take control of it and take your life back.

This post was made using the Auto Blogging Software from WebMagnates.org This line will not appear when posts are made after activating the software to full version.

READ MORE - It seems the older we get the more responsibilities we have and the less time we have to see to them. There are so many things that go on in day to day life it's no wonder that people get overwhelmed and stressed out. Work is a primary concern for many people. This is because most people must work in order to provide a life for themselves and their families.