New Database - Primary Key

[David W. Fenton]

There were once very good
arguments for using surrogate keys even if a table had a real
primary key, but I believe that they have mostly gone away.

I will use a natural key if:

1. it's not functioning as foreign key in another table,

AND/OR

2. it's a single-column key.

I'll use a single-column natural key as a foreign key, but never a
multi-column natural key.

Also, natural keys make join tables extraordinarily ugly and
difficult to work with.

And the benefits are so very slight that it's not worth the
complications.

Perhaps it's a result of the particular apps I have been called upon
to build, but I've almost never encountered a single table in any of
the apps I've built (other then the tables that meet the criteria
above) where a multi-column natural key was possible. My customer's
data is never complete enough to allow it (i.e., they need Nulls).

 

[David W. Fenton]

There are lots of situations where there isn't any alternative to
surrogate keys, in fact the majority of tables I have come across
don't have a good natural key. But in any system that I manage the
DBA will have to justify the decision to use surrogates on a
case-by-case basis.

Here you are being reasonable, and I was hoping for a catfight!

I was actually expecting you to argue for a zero-length string as
default value. I hate that (I despise the fact that A2003 changed
the default text field to allow ZLS), because you can no longer test
for Null, which in an indexed field is faster than testing for a ZLS
(at least with Jet).

I never do it, but some people allow ZLS and initialize to Null, and
then use the ZLS to show that there is no value, not that it is
unknown. That's valid, but it would drive me crazy to have both ZLS
and Null in the same column. I'd always end up getting bad
resultsets because I'd forget to test for both.

 

[Bernard Peek]

They are idiots in that group.

There certainly are. But there's some gold amongst the dross. I've
learned a lot over the years. I've just recommended this group to
someone who foolishly asked for help there and just got abuse from
certain parties..

None of them really work with actual
client data, so far as I can tell. Celko is one of the worst
blowhards -- a smart man, but doesn't really understand the real
world.

Theory is a starting place for designing a real-world app. It is not
the end point.

I used to run a management training course. Typically at least one
person in each intake would declare that as management theories never
work they weren't worth studying. That's why I had to start my courses
with a session on the theory of theories, and how to use them. I had to
point out that management theory is an aid to good management, not a
substitute for it. Understanding the theoretical basis for something can
help eliminate a lot of fruitless work and identify risks before they
become bear-traps.

 

[David W. Fenton]

Understanding the theoretical basis for something can
help eliminate a lot of fruitless work and identify risks before
they become bear-traps.

Absolutely. But theory shouldn't straitjacket your implementation.
As I said, it's a starting point -- you try to normalize as much as
you can until the point at which it causes problems that make the
app unnecessarily complicated.

Some people seem to believe that there's a single proper structure
for representing any particular entity. I believe that there is not,
as the proper structure will depend on the role that entity plays in
the particular application.

I could go on for pages elucidating that point, but I think you get
it, so there's no need.

 

[Tony Toews [MVP]]

They are idiots in that group. None of them really work with actual
client data, so far as I can tell.

Some folks in there are certainly idiots. Actually I do suspect many
do work with real data. Trouble is I suspect a few/some/many don't
work with the application development environment. So they don't have
a clue what works and doesn't work in the real world. If so I pity
the folks who have to work with them.

Tony