disagreement over PK "naming convention"

I'm working with a DBA that wants to have the PK for a personnel table be a
uer's network ID. I asked him what happens when the network ID changes, and
(even though there are tables that use it as a FK) he didn't seem to think
it was going to be a problem
Now the way I was raised as a DBA, you just didn't do this - PK's are
usually int identity, and never meaningful names... but what do I know?
Before I disagree more assertively with him, could there be a legitimate
reason, a different line of thought I've missed, regarding database design?
PaulPrimary key names (like table names) must be unique within the database.
Therefore, it makes sense to name the key something like PK_<table name>.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%232WoT3jrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I'm working with a DBA that wants to have the PK for a personnel table be
> a uer's network ID. I asked him what happens when the network ID changes,
> and (even though there are tables that use it as a FK) he didn't seem to
> think it was going to be a problem
> Now the way I was raised as a DBA, you just didn't do this - PK's are
> usually int identity, and never meaningful names... but what do I know?
> Before I disagree more assertively with him, could there be a legitimate
> reason, a different line of thought I've missed, regarding database
> design?
> Paul
>|||PJ6 wrote:
> I'm working with a DBA that wants to have the PK for a personnel
> table be a uer's network ID. I asked him what happens when the
> network ID changes, and (even though there are tables that use it as
> a FK) he didn't seem to think it was going to be a problem
> Now the way I was raised as a DBA, you just didn't do this - PK's are
> usually int identity, and never meaningful names... but what do I
> know? Before I disagree more assertively with him, could there be a
> legitimate reason, a different line of thought I've missed, regarding
> database design?
>
Oh no! You don't know what you've started!
It's an endless debate without resolution:
http://groups.google.com/groups?q=n...:en&sa=N&tab=wg
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:eFucZ7jrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> PJ6 wrote:
> Oh no! You don't know what you've started!
> It's an endless debate without resolution:
> http://groups.google.com/groups?q=n...:en&sa=N&tab=wg
>
But it's fair to say that there is not a consensus that would make your DBA
friend "wrong". I think that everyone would agree that primary keys should
be updated only rarely, and that doing so can be a hastle. However SQL
Server does support ON UPDATE CASCADE foreign key relationships for exactly
this scenario.
If you have a need to track users across changes in their NetworkID then you
have a valid _modeling_ argument to present against using NetworkID as the
PK. I would steer clear of the natural/surrogate key debate, however.
David|||You would expect each personnel's UserID to be unique at any given
moment in time, however, network IDs certainly do change on occasion,
especially if you have a merger between two previously unrelated
corporations or organizations. The keys used to enforce referential
integrity must be consistent and reliable across time, but human derived
coding schemes are frequently not.
I once worked as a contractor for an organization whose email and
network ids were based on first initial / last name. The company acquired a
new executive vice president who was much chagrined that an existing
employee, who had the same first initial/last name, was getting his email
sent from people who didn't bother to lookup the appropriate address. So, he
arm twisted the network admin into re-assinging email IDs to suit his own
personal need, and in the process they totally screwed Exchange server to
the point where the ID had to be deactivated alltogether.
That's the benefit of using a system generated identity value for the
primary key. Let the world plod along as it may, but at least your records
will always be intact.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%232WoT3jrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I'm working with a DBA that wants to have the PK for a personnel table be
> a uer's network ID. I asked him what happens when the network ID changes,
> and (even though there are tables that use it as a FK) he didn't seem to
> think it was going to be a problem
> Now the way I was raised as a DBA, you just didn't do this - PK's are
> usually int identity, and never meaningful names... but what do I know?
> Before I disagree more assertively with him, could there be a legitimate
> reason, a different line of thought I've missed, regarding database
> design?
> Paul
>|||It′s a religious war :-D
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bob Barrows [MVP]" wrote:

> PJ6 wrote:
> Oh no! You don't know what you've started!
> It's an endless debate without resolution:
> http://groups.google.com/groups?q=n...:en&sa=N&tab=wg
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||Without entering the natural/surrogate keys debate (personally, I stopped
using natural keys and double fields keys many years and since then, more
than half of my problems with databases have disappeared); the use of ON
UPDATE CASCADE foreign key relationships are often useless when your are
dealing with multiple databases on multiple servers or with printed reports.
When you see someone entering your office with a set of reports and bills 6
months old from a (big) client and say that it's now the time to correct
some kind of error(s) and give him his refund, you might be in a big trouble
if you have used natural keys that has changed since then, for exemple
because of a mislabelling or a key-punch error or whatever else.
The use of ON UPDATE CASCADE foreign key relationships is perfect in some
kinds of scenarios - for exemple for storing temporary identity values for
users on the road - but otherwise, it's a disaster waiting to happen.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eh%23rRFkrFHA.2604@.TK2MSFTNGP14.phx.gbl...
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:eFucZ7jrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> But it's fair to say that there is not a consensus that would make your
> DBA friend "wrong". I think that everyone would agree that primary keys
> should be updated only rarely, and that doing so can be a hastle. However
> SQL Server does support ON UPDATE CASCADE foreign key relationships for
> exactly this scenario.
> If you have a need to track users across changes in their NetworkID then
> you have a valid _modeling_ argument to present against using NetworkID as
> the PK. I would steer clear of the natural/surrogate key debate, however.
>
> David
>|||Ignore that. I initially though you were asking about how to name the
primary key.
"JT" <someone@.microsoft.com> wrote in message
news:uaBUH7jrFHA.1168@.TK2MSFTNGP11.phx.gbl...
> Primary key names (like table names) must be unique within the database.
> Therefore, it makes sense to name the key something like PK_<table name>.
> "PJ6" <nobody@.nowhere.net> wrote in message
> news:%232WoT3jrFHA.3424@.TK2MSFTNGP14.phx.gbl...
>|||The DBA you're working with is either (1) a neophyte, (2) a complete moron,
(3) short and doesn't care, (4) a plant from a competitor, (5) trying to
ensure his own job security by building a system that only he can maintain,
or (6) one of Joe Celko's students.
You have my sympathy!
"PJ6" <nobody@.nowhere.net> wrote in message
news:#2WoT3jrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I'm working with a DBA that wants to have the PK for a personnel table be
a
> uer's network ID. I asked him what happens when the network ID changes,
and
> (even though there are tables that use it as a FK) he didn't seem to think
> it was going to be a problem
> Now the way I was raised as a DBA, you just didn't do this - PK's are
> usually int identity, and never meaningful names... but what do I know?
> Before I disagree more assertively with him, could there be a legitimate
> reason, a different line of thought I've missed, regarding database
design?
> Paul
>|||>> Now the way I was raised as a DBA, you just didn't do this - PK's are usu
ally INTEGER identity, and never meaningful names... but what do I know? <<
No, IDENTITY cannot be a key by definition. A key is a subsert of
attributes whose valeus are unique within an entity. You really need
to read some of Dr. Codd's stuff.
If your company issues a network ID with employment to everyone, this
could work. Set up the proper DRI actions (ON UPDATE CASCADE) and the
SQL engine will handle changes.
Besides uniqueness, yoiu also want to have keys that can be verified
and validated. In this case, they are both easy to test -- just ping
the guy to see if the id matches to the person. This is why so many
e-commerce sites use your email as your log on.