Re: Database design issue: seems really bad, yet I'm having trouble coming up with anything better

Couple of points...

People don't really have natural keys, though one could argue that SSN is
for people in the US.

That being the case, the number you have is as artificial as any other.
Your problem isn't the choice of key, your problem is the original size
picked. Outgrowing the initial key size is always a possibility.

Do you use 8-byte big integers for your identity columns or are you using 4
bytes for some? Even with 8 bytes, what happens in 10, 15, 20 years when
the DB supports 16byte integers and you want to add
the 9,223,372,036,854,776th row?



On Thu, Dec 11, 2014 at 2:00 PM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:

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.