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.