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

One last thing, of course 8 byte integers have a downfall. However I'm betting in 99.9% of environments that will be sufficient to long outlast anything you can throw at it.

However, you can do what a lot of commericial products do nowadays and use GUID's as the surrogate keys.

Lets see if you ever get to the 5,316,911,983,139,663,491,615,228,241,121,400,000 combinations that option gives you.



-----Original Message-----
From: Charles Wilt [mailto:charles.wilt@xxxxxxxxx]
Sent: Thursday, December 11, 2014 1:22 PM
To: Midrange Systems Technical Discussion
Subject: 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.

--
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.