I guess I have had the opposite experience when writing SQL with surrogate
Most query tools these days will automatically make the JOIN associations
for you when you have single surrogate keys when you use the same column
name in both tables you need to join to.
Also natural keys have been a bane to my existence. Let me give you a
We have a large inhouse CRM system. It uses a natural key which is an ID
number of an individual. That natural key is used in hundreds of tables to
join to that one master file.
Guess what, that natural key is no longer sufficient (not long enough and
of the correct data type) to store enough ID numbers.
Now we are tasked with finding all associated tables and increase the
length, and recompile all the RPG programs.
If the original designers of this database had used surrogate keys we
wouldn't be in this 100's of hours of programming time mess. They could
just increase the length of that column in ONE table (the master table) and
called it a day.
From: John Yeung [mailto:gallium.arsenide@xxxxxxxxx]
Sent: Thursday, December 11, 2014 10:35 AM
To: Midrange Systems Technical Discussion
Subject: Re: Database design issue: seems really bad, yet I'm having
trouble coming up with anything better
On Thu, Dec 11, 2014 at 10:18 AM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:
In my mind I always pick a single surrogate key for all tables. I stayaway from composite keys at all cost.
"All cost" is a pretty high bar. That's getting into religious territory,
which is treacherous.
I think it's OK to have a policy of always creating a surrogate key.
I mean, if that's a shop standard, eventually everyone will get used to it.
But I definitely feel at least one of your supposed advantages is highly
5. Natural Keys especially composite keys make writing code a pain. Whenyou need to join 4 tables the "where clause" will be much longer (and
easier to mess up) than when single surrogate keys were used.
There can be a mental trade-off. I think it's easier to *think* about
keys that *mean something*. An arbitrary unique ID doesn't just cost the
extra column and the potential extra index and the potential extra database
constraints, it costs brain cycles. It costs the time that I need to get
out pencil and paper just to reason about the simplest things or debug a
When I was working on a (disastrously failed) project where every single
table had its own unique, meaningless key, I found writing code for that to
be a horrible pain. Composite natural keys may require more keystrokes to
type, but I was much LESS likely to mess them up because I knew what they
stood for, and I could type them much faster because they were in my head
and just flowed out.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives