I used to not be a fan of the surrogate key approach until I had to communicate with a SQL Server database that (of course) used them. I've since realized the advantages although I've not had an opportunity to use it on a decent scale.

I look at it as being somewhat analogous to the "All problems in computer science can be solved by another level of indirection". After dealing with some conversions similar to what Matt describes, you'll appreciate it even more.

As John Y. says, you do need to adjust your thinking and it's not as "natural" a thought process. But, there is definitely a positive payback on that investment.

Roger HarmanCOMMON Certified Application Developer – ILE RPG on IBM i on PowerOCEAN User Group – Vice-President, Membership (2014)

From: Matt.Olson@xxxxxxxx
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Database design issue: seems really bad, yet I'm having trouble coming up with anything better
Date: Thu, 11 Dec 2014 19:00:41 +0000

I guess I have had the opposite experience when writing SQL with surrogate keys.

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 quick example:

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.



-----Original Message-----
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 stay away 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 debatable:

5. Natural Keys especially composite keys make writing code a pain. When you 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 data-related issue.

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.

John Y.
--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].