Recently I’ve been working with HES
data on a project, and the current structure of tables we have is for each of the different datasets (Inpatients,
Outpatients, Emeregency Care, etc.) we have a separate table for each financial year. Within these there will be a
ATTENDKEY which is the primary key in that table. This key is stored as a
This key restarts counting each year… well, sort of. Sometimes the minimum value is 1, other times it’s in the 100,000,000’s.
What I wanted to do was build a single table that had all of the inpatients records (and later, do the same for the
other datasets). Because the
EPIKEY values may repeat in different years I need someway to make these unique. One way
of achieveing this would be to create the primary key as a composite of
This is what I have seen done in other databases, but (in my opinion) it’s a very dangerous approach to take. Why? Because it’s far to easy to write a query that joins to other tables and forget to include all of the columns in the composite key, for instance:
SELECT stuff FROM inpatients i INNER JOIN inpatients_diagnoses d ON i.EPIKEY = d.EPIKEY
And I’ve seen many, many, analysts fall into this trap. It’s not always a case of simply forgetting either. Many times people will approach a new database and start just trying to write a query without looking at a database diagram (if one exists), or inspecting the primary/foreign key contraints on a table (and all too often I’ve seen databases created without these being implemented). I think this is exacerbated because we treat SQL as something that analysts must learn, but forget to teach them any theory about databases and their structure.
So, what is the solution to this? What I did was rename the
EPIKEY column to
ORIG_EPIKEY (rather than dropping it
and finding out we needed it later), and adding a new
EPIKEY column of type
UNIQUEIDENTIFIER. Now I have a single
column as the primary key, so my above query works, reducing the risk of incorrect joins.
A UUID (Universally Unique Identifier) is, for practical purposes, unique. As in, it’s unique in any system that may ever exist. This it turns out is a very useful property. If we build our database tables to use a UUID as the primary key, we do not need it to be composite with any other columns.
And there is an added benefit. Consider we have two tables,
table_b that both use an incrementing
integer as the primary key. We could join
table_b together on their primary keys, and the database will
happily spit out results. But they may be completely useless. If however we use a UUID, then the join will only ever
return results where the keys match. If the keys were generated separately, then there is no chance of a collision
table_b, so any query that tries to join these two will result in 0 rows of data (this would be
a good indicator to anyone that the query isn’t correct). If, and only if, the keys were generated in one place, say
table_a, and then used as a foreign key in
table_b would our query work. This is a great guarantee that our results
are correct and valid.
Of course, there is a downside to using this datatype. For one, it’s slower to generate than an autoincrementing number.
I’ve never found generating these values to be terribly slow however. The big downside is storage: a typical ID column
may be a
BIGINT which takes up 8 bytes, whereas a UUID is 128 bytes, 16 times bigger. In certain circumstances these
two may be just enough of a difference to cause an issue.