Hi All!
We are doing new development for SQL Server 2000 and also moving from
SQL 7.0 to SQL Server 2000.
What are cons and pros for using IDENTITY property as PK in SQL SERVER
2000?
Please, share your experience in using IDENTITY as PK .
Does SCOPE_IDENTITY makes life easier in SQL 2000?
Is there issues with DENTITY property when moving DB from one server
to another? (the same version of SQL Server)
Thank you in advance,
Andy
Jul 20 '05
112  10197 
"Steve Kass" <sk***@drew.edu> wrote in message
news:3F**************@drew.edu...
Stijn Verrept wrote:
"David Portas" <RE****************************@acm.org> wrote in message
news:Nv********************@giganews.com...
Do you really allow the same Doctor, Department, etc to appear twice in
its
table with different keys? If you don't declare unique natural keys then
that's the kind of problem you have. An IDENTITY isn't a *surrogate* key
at
all unless the table also has a natural key - it's just a physical row
identifier.
I never said I allow them to appear twice in the column, you have Unique
Constraint for that. I could use that as a natural key, but I prefer
usingan int or smallint. I don't want to note Name, Firstname, ... in another
table as foreign key! Also in the application I don't see me writing:
select SN_Active from seniors where (SN_Name = :SNName) and (SN_FirstName
=:FirstName) and (SN_BirthDate = :SNBirthDate).
Stijn Verrept.
How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity
column and putting the PRIMARY KEY NONCLUSTERED constraint on the
multi-column primary key?
Your question demonstrates profound confusion between logical and physical.
Uniqueness is a logical constraint. Clustering is purely physical and is an
attribute of an index not of a constraint. I realize that SQL confuses the
issue by inappropriately making uniqueness a property of a physical index
structure, but I see no reason to further confuse the issue.
Bob Badour wrote: "Steve Kass" <sk***@drew.edu> wrote in message
news:3F**************@drew.edu...
Stijn Verrept wrote:
"David Portas" <RE****************************@acm.org> wrote in message
news:Nv********************@giganews.com...
Do you really allow the same Doctor, Department, etc to appear twice in
its
table with different keys? If you don't declare unique natural keys then
that's the kind of problem you have. An IDENTITY isn't a *surrogate* key
at
all unless the table also has a natural key - it's just a physical row
identifier.
I never said I allow them to appear twice in the column, you have Unique
Constraint for that. I could use that as a natural key, but I prefer
using
an int or smallint. I don't want to note Name, Firstname, ... in another
table as foreign key! Also in the application I don't see me writing:
select SN_Active from seniors where (SN_Name = :SNName) and (SN_FirstName
=
:FirstName) and (SN_BirthDate = :SNBirthDate).
Stijn Verrept.
How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity
column and putting the PRIMARY KEY NONCLUSTERED constraint on the
multi-column primary key?
Your question demonstrates profound confusion between logical and physical.
Uniqueness is a logical constraint. Clustering is purely physical and is an
attribute of an index not of a constraint. I realize that SQL confuses the
issue by inappropriately making uniqueness a property of a physical index
structure, but I see no reason to further confuse the issue.
Then don't.
Or else be honest. If you see no reason to confuse the issue further,
why quote me out of context by deleting my next paragraph, which made it
clear I was joking? [In case it's not clear, this is a rhetorical
question. I don't really want an answer from you.]
SK
"Bob Badour" <bb*****@golden.net> wrote in message
news:Z-********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl... The problem is with how it is used. If you start giving users access to
identity based values, you get into a bad spot where they want to make
changes to the value (in my line of work, we don't like the numbers 666
in account numbers) so using identities for user values is a bad idea. I
use them only for internal pointers that are never presented to users, since
they are not modifiable. I could use guids, or characters, or whatever for keys and no one would be the wiser.
Keys are logical identifiers. They identify data for the user as well as
for the dbms. Preventing the user from seeing the identifier is just stupid.
Why? Do you want the user typing, remembering, or dealing with the
difference between ID=320983902 and 320984902 or 320983903? I certainly
don't. Invariably they would want something that they understood. Or what
about a GUID: 6969B66E-6A7A-4E89-B2D9-B35799B335C1 vs
DCF5DBC5-73B5-4009-9BBC-9312CFD6AD9D. Yick.
Kind of like you have a user name, an email address, fingerprints, an SSN
and DNA, etc that all identify you, but you only use a few of them here.
DNA is ugly, but as perfect of an identifier, but I don't know what my DNA
is, but it still exists.
Natural keys are nothing more than familiar surrogates.
You are kind of right here, but it is generally true that natural keys
can change, because in the world, things can change. Identities cannot
change.
It is not a "kind of" rightness. The statement is obviously and
self-evidently right to anyone with at least a minimal education in the
fundamentals of data management.
I suppose. But I don't think that the statement means anything. I don't
agree that all natural keys are familiar surrogates. Not every natural key
was initally randomly chosen. Some level of thought was placed to choosing
a name, hence it is not just a familar surrogate. A surrogate key (the word
surrogate meaning to take the place of, and the definition of a surrogate
key is: A unique primary key generated by the RDBMS that is not derived from
any data in the database and whose only significance is to act as the
primary key. I would remove the word primary from the sentence and we have
what I would agree with the definition)
They are very much alike, but I don't think you can state that a natural key
is a surrogate, ever. They are both keys, in that they both are
determinants, which is why we have called them keys.
--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"Stijn Verrept" <sv******@nospan.vub.ac.be> wrote in message
news:OF**************@TK2MSFTNGP11.phx.gbl... In cases like e-mail address and SSN (and in fact most cases), I still
prefer your route, where there is a surrogate key (IDENTITY) that
prevents me from having to cascade changes all over the place, and store larger
foreign keys.
Ok I agree that sometimes natural keys are good. But mind the word:
NATURAL KEYS ARE ALWAYS GOOD. I was standing on my desk shouting if you
didn't see it :)
Just not always preferrable as PRIMARY KEYS! Always put keys on EVERY
unique combination (that doesn't include other unique combiniations ( if ID
is a unique key, then ID, Name should not be, use a simple index in this
case) for the sake of your data. Look up Boyce Codd normal form for more
information.
--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
sometimes :). After reading through this thread I got the impression that
natural keys are used more than surrogate keys, while in practice I use
identity with surrogate keys almost all the time.
Now, you might think, "why not bring SSN into the FirstName + MiddleName
+ LastName key? That would make it unique." Yes, and hideously large.
If SSN is unique, then why not just use SSN as the key? Again, it's large even on its own (CHAR(9) and surely to become CHAR(10) in our lifetimes), so
I fail to see the benefit of repeating the value in every related table,
DRI or not.
Indeed, and I don't know about American legislation but maybe in the
future (or even now) you can have people work for you who don't have a SSN (who
work from a distance country for example) and then you'll get stuck again.
Sorry about the earful, sometimes I get a little typographical diarrhea.
Hopefully that was at least marginally intelligible.
It was :)
Stijn Verrept.
BINGO! This is the point that seems to be missed by most every one. Thank
you David
--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"David Portas" <RE****************************@acm.org> wrote in message
news:Nv********************@giganews.com... Do you really allow the same Doctor, Department, etc to appear twice in
its table with different keys? If you don't declare unique natural keys then
that's the kind of problem you have. An IDENTITY isn't a *surrogate* key
at all unless the table also has a natural key - it's just a physical row
identifier.
--
David Portas
------------
Please reply only to the newsgroup
--
"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:#7**************@TK2MSFTNGP09.phx.gbl...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Z-********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl... The problem is with how it is used. If you start giving users access
to identity based values, you get into a bad spot where they want to make
changes to the value (in my line of work, we don't like the numbers
666 in account numbers) so using identities for user values is a bad idea. I use them only for internal pointers that are never presented to users,
since they are not modifiable. I could use guids, or characters, or
whatever for keys and no one would be the wiser.
Keys are logical identifiers. They identify data for the user as well as
for the dbms. Preventing the user from seeing the identifier is just stupid.
Why? Do you want the user typing, remembering, or dealing with the
difference between ID=320983902 and 320984902 or 320983903?
That depends. If I were creating an identifier for a credit card, I would
want at least seven more digits, and yes I would want users typing,
remembering, swiping and dealing with the numbers.
Preventing the user of the data from seeing the identifier for the data is
just plain stupid.
"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:#7**************@TK2MSFTNGP09.phx.gbl...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Z-********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl...
Kind of like you have a user name, an email address, fingerprints, an SSN
and DNA, etc that all identify you, but you only use a few of them here.
DNA is ugly, but as perfect of an identifier, but I don't know what my DNA
is, but it still exists.
You seem ignorant of a few basic facts regarding DNA. Multiple individuals
(identical twins as well as artificial clones) largely share
indistinguishable DNA. Almost all individuals have multiple DNA patterns due
to viral infections and random mutations. Some individuals, chimeras, have
multiple DNA patterns that are very different. The use of stem cell
treatments will introduce additional DNA patterns into individuals.
Before I die, I hope that none of my cells have my original DNA. Of course,
I also hope that won't be for several millenia at least.
Oh, and DNA fails the simplicity criterion for effective logical references.
"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:#7**************@TK2MSFTNGP09.phx.gbl... "Bob Badour" <bb*****@golden.net> wrote in message
news:Z-********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl... > Natural keys are nothing more than familiar surrogates.
You are kind of right here, but it is generally true that natural keys can change, because in the world, things can change. Identities cannot change.
It is not a "kind of" rightness. The statement is obviously and
self-evidently right to anyone with at least a minimal education in the
fundamentals of data management.
I suppose. But I don't think that the statement means anything.
Then I can only conclude you lack the ability to comprehend relatively
simple written english.
I don't
agree that all natural keys are familiar surrogates. Not every natural
key was initally randomly chosen.
No key is randomly chosen and nothing in surrogacy implies or suggests
randomness. A truly random choice would suggest a psychotic break with
reality. Nothing that anyone can use to identify me is me, and in that sense
any key that identifies me is only a surrogate for me.
Only values are self-identifying, but we use representations as surrogates
even for values.
"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:#7**************@TK2MSFTNGP09.phx.gbl...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Z-********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl... Some level of thought was placed to choosing
a name, hence it is not just a familar surrogate.
Again, I suggest the above sentence suggests you lack the ability to
comprehend relatively simple written english. Perhaps, if you opened a
dictionary and looked up the word 'surrogate', you might improve your
competence at extracting meaning from english.
A surrogate implies no particular level of thought.
A surrogate key (the word
surrogate meaning to take the place of, and the definition of a surrogate
key is: A unique primary key generated by the RDBMS that is not derived
from any data in the database and whose only significance is to act as the
primary key. I would remove the word primary from the sentence and we
have what I would agree with the definition)
That might be a good definition of an IDENTITY column, but it has no bearing
on surrogate keys. By equating the identity columns with surrogate keys, you
only confuse yourself and potentially any similarly uneducated readers.
Nothing about a surrogate key requires a DBMS to generate it.
"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:#7**************@TK2MSFTNGP09.phx.gbl... "Bob Badour" <bb*****@golden.net> wrote in message
news:Z-********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl... They are very much alike, but I don't think you can state that a natural
key is a surrogate, ever. They are both keys, in that they both are
determinants, which is why we have called them keys.
I am not my name and my name is not me. My name is a surrogate for me chosen
for simplicity (simple for an english speaker to say), familiarity (familiar
for english speakers) and stability (my name changes rarely).
Beyond that, I have been well conditioned to respond to my name, which makes
it useful for those who wish to get my attention. Although, given the
similarity in pronunciation to "Mom", this conditioning can be somewhat
inconvenient at the mall and in restaurants.
> Then I can only conclude you lack the ability to comprehend relatively simple written english.
Geez, why are you so hateful and insulting? I have absolutely no interest
in following a conversation where every second post has a useless barb or ad
hominen attack.
Die, thread, die.
--
Aaron Bertrand
SQL Server MVP http://www.aspfaq.com/
On Wed, 3 Dec 2003 09:17:35 -0500 in comp.databases, "Bob Badour"
<bb*****@golden.net> wrote:
With all due respect, the whole point of the witness protection programme is
to prevent people from associating the individual with their previous
identity.
Of course, but for the FBI's database... <g> It was just an example of
a reason for a SSN changing.
--
A)bort, R)etry, I)nfluence with large hammer.
"Bob Badour" <bb*****@golden.net> wrote in message
news:Oq********************@golden.net...
Then I can only conclude you lack the ability to comprehend relatively
simple written english.
Well, then apparently you are so good at English, and less good at being
precise. If natural keys and surrogate keys were in fact the same thing,
then why would we have multiple terms for these things? They are different,
as far as building databases is concerned. Surrogates keys, as defined, are
keys used instead of a natural key. And as far as the following:
That might be a good definition of an IDENTITY column, but it has no
bearingon surrogate keys. By equating the identity columns with surrogate keys,
youonly confuse yourself and potentially any similarly uneducated readers.
Nothing about a surrogate key requires a DBMS to generate it.
I did not come up with any of this terminology. An identity is an
artificial key. It is used as a surrogate key. It is not a natural key. A
surrogate key is not required to be created by the DBMS, but there is no
reason why we cannot us a mechanism built in to provide it.
I am not my name and my name is not me. My name is a surrogate for me
chosenfor simplicity (simple for an english speaker to say), familiarity
(familiarfor english speakers) and stability (my name changes rarely).
You name is part of what makes you you, because everyone has a name. It is
technically not a reasonable key, but it possibly part of a key (like the
MPAA does with actor/director names, just by adding a number, or forcing a
name change.)
Why? Do you want the user typing, remembering, or dealing with the
difference between ID=320983902 and 320984902 or 320983903?
That depends. If I were creating an identifier for a credit card, I would
want at least seven more digits, and yes I would want users typing,
remembering, swiping and dealing with the numbers.
Strangely enough, this is done with credit cards specifically so it will be
hard to rememeber. On the other hand, I would not use a credit card number
as the primary key of a credit card table. Why? Because I would not want
that key spread around the database in other tables. A surrogate key here
would be used to obscure that fact, leaving only a single point of contact
with credit card numbers to be secured.
Preventing the user of the data from seeing the identifier for the data is
just plain stupid.
Yes, I am plain stupid. That is why when you go to a form to choose the
type of credit card you are going to use it shows you 320983902-Visa,
320984902-MasterCard, 320983903-Etc. Or even VS, MC, ET. No, humans like
to see Visa, MasterCard, etc. I am not implying that a user would be
disallowed from using a value, just that there is little reason to ever
present an ugly value to a user, except when that is precisely the desire.
Why I am plain stupid is that I am continuing this discussion and getting
called stupid every few seconds by a person who is not actually having a
discussion that could possibly lead anywhere. If you don't see my point, and
I clearly cannot see your point through all of the muck and anger, then why
waste time. I have these discussions so I can improve my opinions that I
have carefully crafted over 11 years, and that I frequently give to others.
If it is a matter of taste, then I don't mind anyhow. I like hearing others
opinions, and as to why my ideas are wrong if they are (and some are.)
--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:uO**************@tk2msftngp13.phx.gbl... "Bob Badour" <bb*****@golden.net> wrote in message
news:Oq********************@golden.net...
Then I can only conclude you lack the ability to comprehend relatively
simple written english.
Well, then apparently you are so good at English, and less good at being
precise. If natural keys and surrogate keys were in fact the same thing,
then why would we have multiple terms for these things?
I don't recall saying they are the same thing. I recall saying that one is a
subset of the other. Perhaps, if you had better grasp of written english,
you would have observed that the first time. I see nothing imprecise about
what I said.
That might be a good definition of an IDENTITY column, but it has no
bearingon surrogate keys. By equating the identity columns with surrogate keys,
youonly confuse yourself and potentially any similarly uneducated readers.
Nothing about a surrogate key requires a DBMS to generate it.
I did not come up with any of this terminology.
That's obvious. You do not comprehend the terminology either.
An identity is an
artificial key.
It is an rdbms generated key.
It is used as a surrogate key.
All keys are surrogates.
It is not a natural key.
While the key remains unfamiliar, this is true. However, as soon as it
becomes familiar, this ceases to be the case. I was assigned an arbitrary
nine digit university identification number two degades ago. I can still
recite this number easily having used it on literally hundreds if not
thousands of pieces of correspondence. That sounds like a natural enough key
to me.
A
surrogate key is not required to be created by the DBMS...
Well, then, let's omit that requirement from your earlier definition and see
whether natural keys are surrogates:
"A surrogate key (the word surrogate meaning to take the place of, and the
definition of a surrogate key is: A unique primary key that is not derived
from any [other] data in the database and whose only significance is to act
as the primary key."
I suggest we could replace "the primary key" with "an identifying attribute"
without any loss of meaning and probably gain some clarity. Cleaning things
up a little:
A surrogate key is a unique identifying attribute that is not derived from
any other data in the database and whose only significance is to act as an
identifying attribute.
Within many contexts, my name is a unique identifying attribute that is not
derived from any other data and whose only significance is to act as an
identifying attribute within those contexts.
Does that not make my name a surrogate key within those contexts?
My name does not suffice within other contexts. Therefore, I have the nine
digit university identification number I mentioned above and a driver's
license number and a social insurance number and a social security number
and a couple of credit card numbers and a whole bunch of bank account
numbers.
Other than familiarity, what distinguishes a natural key like my name from
any other surrogate?
I am not my name and my name is not me. My name is a surrogate for me
chosenfor simplicity (simple for an english speaker to say), familiarity
(familiarfor english speakers) and stability (my name changes rarely).
You name is part of what makes you you, because everyone has a name.
Your statement shows a general lack of imagination. Not every infant is
named at the moment of birth, and my name is not a part of me. It is
external to me, and I do not change when my name changes.
Why? Do you want the user typing, remembering, or dealing with the
difference between ID=320983902 and 320984902 or 320983903?That depends. If I were creating an identifier for a credit card, I would
want at least seven more digits, and yes I would want users typing,
remembering, swiping and dealing with the numbers.
Strangely enough, this is done with credit cards specifically so it will
be hard to rememeber. On the other hand, I would not use a credit card
number as the primary key of a credit card table. Why? Because I would not want
that key spread around the database in other tables. A surrogate key here
would be used to obscure that fact, leaving only a single point of contact
with credit card numbers to be secured.
If you worked for a credit card company, you would see things differently.
After all, the credit card number is an account number for the credit card
company. Their whole business relates to those accounts and they use those
numbers to identify the accounts to the external world including to business
partners and to credit bureaus.
Preventing the user of the data from seeing the identifier for the data
isjust plain stupid.
If you don't see my point, and
I clearly cannot see your point through all of the muck and anger, then
why waste time.
Again, I suggest your perception of anger suggests your ability to
comprehend written english sorely lacks. I invite you to consider whether
you project your own emotional state onto the words you read and to consider
whether this might cloud your ability to interpret the meaning of those
words.
I have these discussions so I can improve my opinions that I
have carefully crafted over 11 years, and that I frequently give to
others.
Some people frequently give others syphyllus, but I would not congratulate
them for the deed. I suggest you get more out of the gift than the others
do.
If it is a matter of taste, then I don't mind anyhow. I like hearing
others opinions, and as to why my ideas are wrong if they are (and some are.)
It is not a matter of taste but a matter of education. By very objective
criteria, hiding the logical identifier from users is just plain stupid. A
user must have access to the logical identifier to properly and to correctly
express queries.
RE/ Please, share your experience in using IDENTITY as PK .
One advantage I see with using IDENTITY PKs (or any blind, dumb number...) is
that I'm protected against my own lack of knowledge.
For instance: I'd guess it's pretty easy to set up a "People" database on the
assumption that social security numbers are unique. The long and short of it
is that they're not. I almost made that mistake once, but my habit of using
surrogate keys whenever possible saved me from getting egg on my face when the
project was about 95% complete.
--
PeteCresswell
"Bob Badour" <bb*****@golden.net> wrote in message
news:Z-********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl... The problem is with how it is used. If you start giving users access to
identity based values, you get into a bad spot where they want to make
changes to the value (in my line of work, we don't like the numbers 666
in account numbers) so using identities for user values is a bad idea. I
use them only for internal pointers that are never presented to users, since
they are not modifiable. I could use guids, or characters, or whatever for keys and no one would be the wiser.
Keys are logical identifiers. They identify data for the user as well as
for the dbms. Preventing the user from seeing the identifier is just stupid.
<snip>
I disagree. In the example I presented at the top of this thread, I use a
vendorID that is used to link the normalized tables to each other. The user
does not care that Bob's Widgets Inc. is number 12345 in the database, they
just know that when they pick Bob's Widgets Inc. in the application, the
data for Bob's Widgets Inc. is presented. Me using an ID under the covers to
link tables is not important to the user.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"Trey Walpole" <tr********@SPcomcastAM.net> wrote in message
news:#G**************@TK2MSFTNGP09.phx.gbl...
<snip> Attributes that are single, [supposedly] unique attributes (e.g., SSN),
usually represent some official, governmentally recognized ID, and
therefore have legal issues with being propagated throughout a system.
<snip>
Not to mention SSN's are not unique.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:en**************@TK2MSFTNGP10.phx.gbl...
<snip> Now, you might think, "why not bring SSN into the FirstName + MiddleName +
LastName key? That would make it unique." Yes, and hideously large. If
SSN is unique, then why not just use SSN as the key? Again, it's large
even on its own (CHAR(9) and surely to become CHAR(10) in our lifetimes), so I
fail to see the benefit of repeating the value in every related table, DRI
or not.
<snip>
The problem there is that SSN's are not unique. I worked on a project for a
military agency a few years back, and in the requirements analysis phase we
talked about using SSN as our unique ID for some of the data. They were at
that point, coincidenetly, dealing with a problem on another system because
they had 2 officers with identical SSN's. The DBA also told us that this was
4th or 5th time they had seen this in the past 10 years of his career there.
I was shocked to say the least, as I always thought SSN was unique.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"Bob Badour" <bb*****@golden.net> wrote in message
news:-f********************@golden.net...
<snip> With all due respect, the whole point of the witness protection programme
is to prevent people from associating the individual with their previous
identity.
So a bad PK would be prefered. :)
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:#f**************@TK2MSFTNGP11.phx.gbl... Then I can only conclude you lack the ability to comprehend relatively
simple written english.
Geez, why are you so hateful and insulting? I have absolutely no interest
in following a conversation where every second post has a useless barb or
ad hominen attack.
Die, thread, die.
I concur, aside from Mr. Badour's immature, and hateful pokes, this thread
has been great. Hopefully he'll go away, or someone will mention hitler so
we can consider this thread dead.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"Bob Badour" <bb*****@golden.net> wrote in message
news:Iu********************@golden.net...
<snip> It is not a natural key.
While the key remains unfamiliar, this is true. However, as soon as it
becomes familiar, this ceases to be the case. I was assigned an arbitrary
nine digit university identification number two degades ago. I can still
recite this number easily having used it on literally hundreds if not
thousands of pieces of correspondence. That sounds like a natural enough
key to me.
<snip>
So, are you saying an arbritrary number assigned to you by your collegis a
natural key because it has become famaliar to you, but an IDENTITY field
can't be?
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:a7********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Z-********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl... The problem is with how it is used. If you start giving users access
to identity based values, you get into a bad spot where they want to make
changes to the value (in my line of work, we don't like the numbers
666 in account numbers) so using identities for user values is a bad idea. I use them only for internal pointers that are never presented to users,
since they are not modifiable. I could use guids, or characters, or
whatever for keys and no one would be the wiser.
Keys are logical identifiers. They identify data for the user as well as
for the dbms. Preventing the user from seeing the identifier is just stupid.
<snip>
I disagree. In the example I presented at the top of this thread, I use a
vendorID that is used to link the normalized tables to each other. The
user does not care that Bob's Widgets Inc. is number 12345 in the database,
With all due respect, the user of the dbms does care. Think about it.
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:a7********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:-f********************@golden.net...
<snip> With all due respect, the whole point of the witness protection
programme is to prevent people from associating the individual with their previous
identity.
So a bad PK would be prefered. :)
Nope. Two identities require two identifiers. A good key will capture that
information correctly.
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:a7********************@giganews.com...
"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:#f**************@TK2MSFTNGP11.phx.gbl... Then I can only conclude you lack the ability to comprehend relatively
simple written english.
Geez, why are you so hateful and insulting? I have absolutely no
interest in following a conversation where every second post has a useless barb
or ad hominen attack.
Die, thread, die.
I concur, aside from Mr. Badour's immature, and hateful pokes, this thread
has been great. Hopefully he'll go away, or someone will mention hitler so
we can consider this thread dead.
I don't hate anyone. I provide truly helpful information to those who
actually want to learn. Making accurate observations regarding the apparent
source of the ignorami's sophistry helps those who might otherwise be duped.
In this particular case, the sophistry was little more than a
deconstructionist denial of meaning (without the actual deconstruction of
course). The man who posted it is nothing more than a self-important, puffed
up, vociferous ignoramus, and it is a service to the world to point out this
fact. Any rational, thinking person should find his post an insult to their
intelligence.
Mr. Vanilla, what was so great about this thread? Did you find it
informative? Did you find comfort in the repetition of your own
misconceptions? What was great?
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:p8********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Iu********************@golden.net...
<snip> It is not a natural key.
While the key remains unfamiliar, this is true. However, as soon as it
becomes familiar, this ceases to be the case. I was assigned an
arbitrary nine digit university identification number two degades ago. I can still
recite this number easily having used it on literally hundreds if not
thousands of pieces of correspondence. That sounds like a natural enough
key to me.
<snip>
So, are you saying an arbritrary number assigned to you by your collegis a
natural key because it has become famaliar to you, but an IDENTITY field
can't be?
I suggest you apparently lack the ability to comprehend relatively simple
written english as well. Could you point out anything I wrote that would
lead you to conclude I ever said anything so ridiculous?
"Bob Badour" <bb*****@golden.net> wrote in message
news:Qa********************@golden.net...
<snip> With all due respect, the user of the dbms does care. Think about it.
Please explain. I do not understand your point. For our application, the
user works an invoice, they want to pick the Vendor by name. We need to
associate that invoice to a vendor, we use the internal ID. Same goes for
contracts, service line items, etc. Using the internal ID allows us to
connect our records, the user doesn't care, nor does s/he need to know about
it. With this design, when the user goes in and changes a vendor name, which
they can and do from time to time, the ID keeps the records linked.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:Qq********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Qa********************@golden.net...
<snip> With all due respect, the user of the dbms does care. Think about it.
Please explain. I do not understand your point.
That's because you have not thought about it yet. Either that or you really
do lack the ability to comprehend simple written english.
"Bob Badour" <bb*****@golden.net> wrote in message
news:J-********************@golden.net...
<snip> I don't hate anyone. I provide truly helpful information to those who
actually want to learn. Making accurate observations regarding the
apparent source of the ignorami's sophistry helps those who might otherwise be
duped.
In this particular case, the sophistry was little more than a
deconstructionist denial of meaning (without the actual deconstruction of
course). The man who posted it is nothing more than a self-important,
puffed up, vociferous ignoramus, and it is a service to the world to point out
this fact. Any rational, thinking person should find his post an insult to
their intelligence.
That is very useful. If you didn't have an apparent knowledge of DBMS's I'd
believe you were a troll.
Mr. Vanilla, what was so great about this thread? Did you find it
informative? Did you find comfort in the repetition of your own
misconceptions? What was great?
I have found the active discussion of pro's/con's to be informative. Clearly
there are some subjective aspects to this topic. If it were clearly an
objective topic, the thread would not have survived this long, and we would
long ago have seen a link to documentation explicitly stating the CORRECT
method. I believe that there is not one clearcut correct way, so the thread
is therefore informative and interesting to me.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"Bob Badour" <bb*****@golden.net> wrote in message
news:DJ********************@golden.net... "BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:p8********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Iu********************@golden.net...
<snip> > It is not a natural key.
While the key remains unfamiliar, this is true. However, as soon as it
becomes familiar, this ceases to be the case. I was assigned an arbitrary nine digit university identification number two degades ago. I can
still recite this number easily having used it on literally hundreds if not
thousands of pieces of correspondence. That sounds like a natural
enough key to me.
<snip>
So, are you saying an arbritrary number assigned to you by your collegis
a natural key because it has become famaliar to you, but an IDENTITY field
can't be?
I suggest you apparently lack the ability to comprehend relatively simple
written english as well. Could you point out anything I wrote that would
lead you to conclude I ever said anything so ridiculous?
I did as you ask in my original post, where I quoted your post. It was my
interpretation of that paragraph that led me to ask the question. I was not
making a statement, I was asking a question. You seemed to say, in the above
paragraph, that the number assigned to you by your college "sounds like a
natural enough key", so I was inquiring as to why this arbitrary number is
any more natural then an IDENTITY field.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"Bob Badour" <bb*****@golden.net> wrote in message
news:ru********************@golden.net... "BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:Qq********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Qa********************@golden.net...
<snip> With all due respect, the user of the dbms does care. Think about it.
Please explain. I do not understand your point.
That's because you have not thought about it yet. Either that or you
really do lack the ability to comprehend simple written english.
With each post, I believe you to be more of a troll. We are discussing a
specific topic here. You have taken issue with one of my questions. If you
will simply make insulting remarks and continue to refrain from answering
the questions with what you believe to be the correct answer, you do nothing
but reduce your credibility.
I understand the design I am discussing, and believe it to be a good one. If
you believe it not to be, explain why.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:6f********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:J-********************@golden.net...
<snip> I don't hate anyone. I provide truly helpful information to those who
actually want to learn. Making accurate observations regarding the apparent source of the ignorami's sophistry helps those who might otherwise be
duped.
In this particular case, the sophistry was little more than a
deconstructionist denial of meaning (without the actual deconstruction
of course). The man who posted it is nothing more than a self-important,
puffed up, vociferous ignoramus, and it is a service to the world to point out
this fact. Any rational, thinking person should find his post an insult to
their intelligence.
That is very useful. If you didn't have an apparent knowledge of DBMS's
I'd believe you were a troll.
Mr. Vanilla, what was so great about this thread? Did you find it
informative? Did you find comfort in the repetition of your own
misconceptions? What was great?
I have found the active discussion of pro's/con's to be informative.
Clearly there are some subjective aspects to this topic. If it were clearly an
objective topic, the thread would not have survived this long, and we
would long ago have seen a link to documentation explicitly stating the CORRECT
method. I believe that there is not one clearcut correct way, so the
thread is therefore informative and interesting to me.
"familiarity, irreducibility, simplicity, stability" http://www.dbdebunk.com/page/page/622344.htm
I don't use reducible keys so I generally simplify the above to familiarity,
simplicity and stability, which I have stated several times in this thread.
Nobody in this thread has contributed anything beyond those criteria;
although, most posters have danced around the issue without actually stating
what the criteria are.
Because most of the contributors to this thread are profoundly ignorant of
fundamentals, they lack the ability to state anything succinctly, and
instead they veer off onto wild tangents where they insist others accept
their ignorant misconceived definitions of simple terms.
Did you really find the thread informative? If so, how were you informed by
the thread? What knowledge did you acquire? Or are you really saying you are
comforted by the idea that you can just accept your subjective biases
without further thought?
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:6f********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:DJ********************@golden.net... "BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:p8********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Iu********************@golden.net...
<snip>
> > It is not a natural key.
>
> While the key remains unfamiliar, this is true. However, as soon as
it > becomes familiar, this ceases to be the case. I was assigned an arbitrary > nine digit university identification number two degades ago. I can still > recite this number easily having used it on literally hundreds if
not > thousands of pieces of correspondence. That sounds like a natural enough key
> to me.
<snip>
So, are you saying an arbritrary number assigned to you by your
collegis a natural key because it has become famaliar to you, but an IDENTITY
field can't be?
I suggest you apparently lack the ability to comprehend relatively
simple written english as well. Could you point out anything I wrote that would
lead you to conclude I ever said anything so ridiculous?
I did as you ask in my original post, where I quoted your post. It was my
interpretation of that paragraph that led me to ask the question. I was
not making a statement, I was asking a question. You seemed to say, in the
above paragraph, that the number assigned to you by your college "sounds like a
natural enough key", so I was inquiring as to why this arbitrary number is
any more natural then an IDENTITY field.
I said nothing to indicate that it is any more natural than an identity
field. I suggest your incapacity with respect to written english amounts to
a failure to apply the closed world assumption.
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:c5********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:ru********************@golden.net... "BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:Qq********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Qa********************@golden.net...
<snip>
> With all due respect, the user of the dbms does care. Think about
it.
Please explain. I do not understand your point.
That's because you have not thought about it yet. Either that or you
really do lack the ability to comprehend simple written english.
With each post, I believe you to be more of a troll. We are discussing a
specific topic here. You have taken issue with one of my questions. If you
will simply make insulting remarks and continue to refrain from answering
the questions with what you believe to be the correct answer, you do
nothing but reduce your credibility.
I understand the design I am discussing, and believe it to be a good one.
If you believe it not to be, explain why.
When the user of the dbms must track down an alleged anomaly in the data,
how does the user proceed?
"Bob Badour" <bb*****@golden.net> wrote in message
news:Dd********************@golden.net...
<snip> Because most of the contributors to this thread are profoundly ignorant of
fundamentals, they lack the ability to state anything succinctly, and
instead they veer off onto wild tangents where they insist others accept
their ignorant misconceived definitions of simple terms.
<snip>
I guess we'll have to agree to disagree, as I do not see your posts
contributing to the thread, and you don't see me doing that as well.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:c5********************@giganews.com... That's because you have not thought about it yet. Either that or you
really do lack the ability to comprehend simple written english.
With each post, I believe you to be more of a troll.
Let me free you of any doubts: if it looks like a troll, smells like a troll
and sounds like a troll: it's a troll!!!
And a boring one too because I hear nothing else but: "lack the ability to
comprehend simple written english.". And I can assure you, I comprehend
simple written English as well as Dutch, French and German :-P
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:-P********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Dd********************@golden.net...
<snip> Because most of the contributors to this thread are profoundly ignorant
of fundamentals, they lack the ability to state anything succinctly, and
instead they veer off onto wild tangents where they insist others accept
their ignorant misconceived definitions of simple terms.
<snip>
I guess we'll have to agree to disagree, as I do not see your posts
contributing to the thread, and you don't see me doing that as well.
I doubt we disagree on the data management issues, and I am quite content to
agree to disagree on the rest.
"Bob Badour" <bb*****@golden.net> wrote in message
news:vt********************@golden.net...
<snip> When the user of the dbms must track down an alleged anomaly in the data,
how does the user proceed?
That is a valid point. My answer would be that we have two levels of users.
The "app users" that never see the ID, would not be tracking such an
anomaly. So that is not an issue for us. If such an anomoly were to arise,
which hopefully the design and proper management would prevent, we have a
level of DB users that could analyze the tables directly to solve the issue
and hopefully provide a solution to prevent further anomolies.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:Ft********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:vt********************@golden.net...
<snip> When the user of the dbms must track down an alleged anomaly in the
data, how does the user proceed?
That is a valid point. My answer would be that we have two levels of
users. The "app users" that never see the ID, would not be tracking such an
anomaly.
In other words, the "app users" use a view of the data with a different
identifier. They see the identifier they use. They might perceive an anomaly
in their data and use the identifier they see to track it down.
The relational model has views and snapshots, ie. named derived relation
variables, to address this very issue. It is revealing to consider the
candidate keys of these derived relation variables.
So that is not an issue for us. If such an anomoly were to arise,
which hopefully the design and proper management would prevent
No amount of design and proper management can eliminate human error or
malice.
, we have a
level of DB users that could analyze the tables directly to solve the
issue and hopefully provide a solution to prevent further anomolies.
These users see a different view of the data or perhaps a proper superset of
the "app users" view of the data. They, of course, can see the identifiers
they use.
"Bob Badour" <bb*****@golden.net> wrote in message
news:7q********************@golden.net... "BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:Ft********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:vt********************@golden.net...
<snip> When the user of the dbms must track down an alleged anomaly in the data, how does the user proceed?
That is a valid point. My answer would be that we have two levels of
users. The "app users" that never see the ID, would not be tracking such an
anomaly.
In other words, the "app users" use a view of the data with a different
identifier. They see the identifier they use. They might perceive an
anomaly in their data and use the identifier they see to track it down.
True.
The relational model has views and snapshots, ie. named derived relation
variables, to address this very issue. It is revealing to consider the
candidate keys of these derived relation variables.
So that is not an issue for us. If such an anomoly were to arise,
which hopefully the design and proper management would prevent
No amount of design and proper management can eliminate human error or
malice.
True, which is why I like the IDENTITY fields in this case. By allowing the
DB to assign a unique key to the records in this particular table, I reduce
the human error component by one by not relying on a user to properly link
this record to others, or improperly break that link with a typo.
, we have a
level of DB users that could analyze the tables directly to solve the issue and hopefully provide a solution to prevent further anomolies.
These users see a different view of the data or perhaps a proper superset
of the "app users" view of the data. They, of course, can see the identifiers
they use.
True.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:Wf********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:7q********************@golden.net... "BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:Ft********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:vt********************@golden.net...
<snip>
> When the user of the dbms must track down an alleged anomaly in the data, > how does the user proceed?
That is a valid point. My answer would be that we have two levels of
users. The "app users" that never see the ID, would not be tracking such an
anomaly.
In other words, the "app users" use a view of the data with a different
identifier. They see the identifier they use. They might perceive an
anomaly in their data and use the identifier they see to track it down.
True.
The relational model has views and snapshots, ie. named derived relation
variables, to address this very issue. It is revealing to consider the
candidate keys of these derived relation variables.
So that is not an issue for us. If such an anomoly were to arise,
which hopefully the design and proper management would prevent
No amount of design and proper management can eliminate human error or
malice.
True, which is why I like the IDENTITY fields in this case. By allowing
the DB to assign a unique key to the records in this particular table, I
reduce the human error component by one by not relying on a user to properly link
this record to others, or improperly break that link with a typo.
I direct you back to my earlier suggestion to consider the candidate keys of
the named derived relation variables the "app users" see. From the
perspective of the "app users", you have not affected anything with respect
to forming proper references or identifiers, and you have introduced an
opportunity for human error on the part of the direct dbms users or
application programmers.
Note, I am not saying that one should never use an identity column or
introduce a simple surrogate; I am only trying to get you to recognize the
real consequences of the decision. Those consequences actually contradict
your statement above.
, we have a
level of DB users that could analyze the tables directly to solve the
issue and hopefully provide a solution to prevent further anomolies.
These users see a different view of the data or perhaps a proper
superset of the "app users" view of the data. They, of course, can see the
identifiers they use.
True.
You are starting to get on my nerves with your claims of having
super-preciseness that no one else has. You state: Well, then apparently you are so good at English, and less good at being
precise. If natural keys and surrogate keys were in fact the same
thing, then why would we have multiple terms for these things?
I don't recall saying they are the same thing. I recall saying that one is
a subset of the other. Perhaps, if you had better grasp of written english,
you would have observed that the first time. I see nothing imprecise about
what I said.
Then you say
It is used as a surrogate key.
All keys are surrogates.
By anyones cound, a natural key must be a key. You state that all keys are
surrogates, hence, due to our old friend the transitive property, all
natural keys are surrogates. .
A surrogate key is a unique identifying attribute that is not derived from
any other data in the database and whose only significance is to act as an
identifying attribute.
By any other data, it means that the value is not based on the existence of
any other data. Otherwise all normalized data (one single source) is by
your definition surrogate data.
You name is part of what makes you you, because everyone has a name.
Your statement shows a general lack of imagination. Not every infant is
named at the moment of birth, and my name is not a part of me. It is
external to me, and I do not change when my name changes.
We are talking about modeling reality in relational database, not reality in
and of itself. Every column that makes up the table becomes part of the
essense of the row/instance of the entity being modeled. Not one column in
our person table reflects any real attribute of my being, as I am a human
being (go ahead, feel free to challenge it :) and as such not electronic.
However, the row in the database models me, and has as much information to
represent me as possible/necessary.
If it is a matter of taste, then I don't mind anyhow. I like hearing
others opinions, and as to why my ideas are wrong if they are (and some are.)
It is not a matter of taste but a matter of education. By very objective
criteria, hiding the logical identifier from users is just plain stupid. A
user must have access to the logical identifier to properly and to
correctly express queries.
Two reasons this is wrong. One, most users do not do any direct querying
into an OLTP database directly. Generally all queries would be built for
them by administrators (possibly the meaning of user as I was using it was
not quite clear, I should have said end users)
But second, if I express the following query:
Select fieldList
from table1
join table2
on table1.table1IdentityKey = table2.table1IdentityKey
When have I ever seen this value? I haven't. This would likely never be
seen, even by administrative users, other than for convienience of not
typing a compound key that might contain a date value, or a guid, or
whatever is needed to naturally identify the row.
I have these discussions so I can improve my opinions that I
have carefully crafted over 11 years, and that I frequently give to
others.
Some people frequently give others syphyllus, but I would not congratulate
them for the deed. I suggest you get more out of the gift than the others
do.
The only think I can think to respond here is "You are a ninny." Though
that is possibly a bit sophisticated a response to such a preposterously
banal comment from someone who has such high regards for his own knowledge.
Again, I suggest your perception of anger suggests your ability to
comprehend written english sorely lacks. I invite you to consider whether
you project your own emotional state onto the words you read and to
consider whether this might cloud your ability to interpret the meaning of those
words.
You are correct. You have been quite nice, and I apologise for miscontruing
your remarks as having any anger or animosity towards myself or any of the
other persons who have responded.
--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"Bob Badour" <bb*****@golden.net> wrote in message
news:Iu********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:uO**************@tk2msftngp13.phx.gbl... "Bob Badour" <bb*****@golden.net> wrote in message
news:Oq********************@golden.net...
Then I can only conclude you lack the ability to comprehend relatively
simple written english.
Well, then apparently you are so good at English, and less good at being
precise. If natural keys and surrogate keys were in fact the same
thing, then why would we have multiple terms for these things?
I don't recall saying they are the same thing. I recall saying that one is
a subset of the other. Perhaps, if you had better grasp of written english,
you would have observed that the first time. I see nothing imprecise about
what I said.
That might be a good definition of an IDENTITY column, but it has no
bearingon surrogate keys. By equating the identity columns with surrogate
keys, youonly confuse yourself and potentially any similarly uneducated readers.
Nothing about a surrogate key requires a DBMS to generate it.
I did not come up with any of this terminology.
That's obvious. You do not comprehend the terminology either.
An identity is an
artificial key.
It is an rdbms generated key.
It is used as a surrogate key.
All keys are surrogates.
You make a good point in your last post, so I'd to like respond by
requesting you show a table definition that supports your argument. My table
for sake of argument has a VENDOR_ID which is an INDENTITY field, and a
VENDOR_NAME field, let's say VARCHAR(50) for sake of argument, which
contains the vendor's name. All other tables, for instance the invoice
table, has a column for VENDOR_ID which links the invoice to a specific
vendor.
What optional design would you propose?
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
If you are replying to one of my messages, I have to remark that I make good
points in all of my posts. I have no desire to waste my time on pointless
messages.
I see nothing in your request to indicate you have understood any point I
have made thus far. I certainly see no reason to think an explicit schema
definition will help you, nor do I see anything relevant in the schema
outline you have given. Either you can comprehend written english and are
willing to think, or not. Either you understand what an antecedent is and
can identify one, or not. Either you will limit your objections to what I
have actually written, or you will argue with your own prejudices.
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:or********************@giganews.com... You make a good point in your last post, so I'd to like respond by
requesting you show a table definition that supports your argument. My
table for sake of argument has a VENDOR_ID which is an INDENTITY field, and a
VENDOR_NAME field, let's say VARCHAR(50) for sake of argument, which
contains the vendor's name. All other tables, for instance the invoice
table, has a column for VENDOR_ID which links the invoice to a specific
vendor.
What optional design would you propose?
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:Oo**************@tk2msftngp13.phx.gbl... You are starting to get on my nerves with your claims of having
super-preciseness that no one else has. You state:
Well, then apparently you are so good at English, and less good at
being precise. If natural keys and surrogate keys were in fact the same thing, then why would we have multiple terms for these things?
I don't recall saying they are the same thing. I recall saying that one
is a subset of the other. Perhaps, if you had better grasp of written
english, you would have observed that the first time. I see nothing imprecise
about what I said.
Then you say
It is used as a surrogate key.
All keys are surrogates.
By anyones cound, a natural key must be a key. You state that all keys
are surrogates, hence, due to our old friend the transitive property, all
natural keys are surrogates. .
Natural keys are a subset of surrogate keys. Where have I suggested anything
different? Had I stated that natural keys and surrogate keys are the same
thing, I would have had to state that surrogate keys are a subset of natural
keys as well as vice versa. I don't recall ever writing any such thing.
A surrogate key is a unique identifying attribute that is not derived
from any other data in the database and whose only significance is to act as
an identifying attribute.
By any other data, it means that the value is not based on the existence
of any other data. Otherwise all normalized data (one single source) is by
your definition surrogate data.
To contradict my statement, all you have to do is identify one concrete
example of a useful key that is not a surrogate for anything.
You name is part of what makes you you, because everyone has a name.
Your statement shows a general lack of imagination. Not every infant is
named at the moment of birth, and my name is not a part of me. It is
external to me, and I do not change when my name changes.
We are talking about modeling reality in relational database, not reality
in and of itself.
You are clutching at straws. Do you lack sufficient intellectual honesty to
cede a point when appropriate?
If it is a matter of taste, then I don't mind anyhow. I like hearing
others opinions, and as to why my ideas are wrong if they are (and some are.)
It is not a matter of taste but a matter of education. By very objective
criteria, hiding the logical identifier from users is just plain stupid.
A user must have access to the logical identifier to properly and to
correctly express queries.
Two reasons this is wrong. One, most users do not do any direct querying
into an OLTP database directly.
All users of the dbms use the dbms. I don't care about users of other things
who do not use the dbms.
[straw man example join snipped]
When have I ever seen this value?
How do you track down an alleged data anomaly reported from the field
without identifying data?
I have these discussions so I can improve my opinions that I
have carefully crafted over 11 years, and that I frequently give to
others.
Some people frequently give others syphyllus, but I would not
congratulate them for the deed. I suggest you get more out of the gift than the
others do.
The only think I can think to respond here is "You are a ninny." Though
that is possibly a bit sophisticated a response to such a preposterously
banal comment from someone who has such high regards for his own
knowledge.
By sophisticated do you mean? Impure? Adulterated? Having used sophistry?
Lacking natural simplicity? Not genuine? Rendered worthless by admixture?
Damaged? Perverted? Debased? Corrupted? Vitiated? http://dictionary.reference.com/search?q=sophisticated
Sophisticated has so many meanings its use is often nebulous. Regardless of
the meaning you intend, your sophistry underwhelms me.
"Bob Badour" <bb*****@golden.net> wrote in message
news:Mp********************@golden.net... If you are replying to one of my messages, I have to remark that I make
good points in all of my posts. I have no desire to waste my time on pointless
messages.
I see nothing in your request to indicate you have understood any point I
have made thus far. I certainly see no reason to think an explicit schema
definition will help you, nor do I see anything relevant in the schema
outline you have given. Either you can comprehend written english and are
willing to think, or not. Either you understand what an antecedent is and
can identify one, or not. Either you will limit your objections to what I
have actually written, or you will argue with your own prejudices.
<snip>
I am not arguing. You misunderstand me. This thread began with me using an
example of an IDENTITY column as foreign key to other tables. You took issue
with this. In my last post, I simply stated a simplified version of my table
design, and requested you provide an alternate configuration that in your
opinion displays the correct setup, as you imply mine is flawed.
I am not arguing. I am simply asking you to show an example of your
theoretical ideas. Instead of responding with an answer, you again resort to
insults.
So I tired being polite. I tried discussing the topic with you. You refuse
to do so.
I yield. BV zero. Troll 1.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
[Followups trimmed. This is not a Microsoft matter.]
In comp.databases Aaron Bertrand - MVP <aa***@trashaspfaq.com> wrote: I am by no means a SQL expert, so forgive me if this seems ignorant...But
why can't the ID columm be a natural key? For example, I am working on a
project that has a vendors table. The list of vendors is used in
relationship to several other tables. We build this table with an identity
An identity value that is generated by the system is not "natural"... a
natural key means that the key is, by nature, identifying a single row...
not artificially because you generated some value for it. A natural key
could be an e-mail address, or a social security number, or a license plate
number, or a latitude and longitude -- something that is part of the data
that also happens to uniquely identify it.
Pardon my delurk: Social Security account numbers do not form a
candidate key for people in the United States. There is no unique
mapping from SSNs onto people, nor from people onto SSNs.
Defeating the mapping from SSNs onto people is the fact that the Social
Security Administration re-uses SSNs after their holders die and
benefits are paid out.
Defeating the mapping from people onto SSNs is the fact that the SSA
has been known to assign to the same person different SSNs over their
lifetime, especially to foreign students who become immigrants.
(ISTR seeing this on someone's online SQL tutorial at one point. It
really underscores for me the idea that relations in a database really
do need to reflect relationships in the world being modeled. It also,
perhaps, shows what happens when you use _someone else's artificial key_
(which is what an SSN is!) as if it were a _natural key_. That way lies
identity theft, or at least identity confusion ....)
--
Karl A. Krueger <kk******@example.edu>
Woods Hole Oceanographic Institution
Email address is spamtrapped. s/example/whoi/
"Outlook not so good." -- Magic 8-Ball Software Reviews
"BenignVanilla" <bv@tibetanbeefgarden.com> wrote in message
news:0c********************@giganews.com...
"Bob Badour" <bb*****@golden.net> wrote in message
news:Mp********************@golden.net... If you are replying to one of my messages, I have to remark that I make good points in all of my posts. I have no desire to waste my time on
pointless messages.
I see nothing in your request to indicate you have understood any point
I have made thus far. I certainly see no reason to think an explicit
schema definition will help you, nor do I see anything relevant in the schema
outline you have given. Either you can comprehend written english and
are willing to think, or not. Either you understand what an antecedent is
and can identify one, or not. Either you will limit your objections to what
I have actually written, or you will argue with your own prejudices.
<snip>
I am not arguing. You misunderstand me. This thread began with me using an
example of an IDENTITY column as foreign key to other tables. You took
issue with this.
I doubt very much that I took issue with a foreign key reference. Perhaps,
if you spent more time observing and thinking instead of assuming and
reacting, you would already know that.
In my last post, I simply stated a simplified version of my table
design, and requested you provide an alternate configuration that in your
opinion displays the correct setup, as you imply mine is flawed.
Where and how did I imply that? As I said in my previous post, "Either you
will limit your objections to what I have actually written, or you will
argue with your own prejudices." My statement was clear and my vocabulary
simple. Either you have the ability to comprehend simple written english, or
you do not.
You claim to have initiated this thread, which is cross-posted to three
newsgroups. You talk past what is written in the thread and refuse to
acknowledge the actual content. Yet, somehow, you claim I am the troll. Is
it not possible that you project your own character defects onto me?
"Bob Badour" <bb*****@golden.net> wrote in message
news:cp********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:Oo**************@tk2msftngp13.phx.gbl... You are starting to get on my nerves with your claims of having
super-preciseness that no one else has. You state:
> Well, then apparently you are so good at English, and less good at being > precise. If natural keys and surrogate keys were in fact the same thing, > then why would we have multiple terms for these things?
I don't recall saying they are the same thing. I recall saying that
one is a subset of the other. Perhaps, if you had better grasp of written english, you would have observed that the first time. I see nothing imprecise about what I said.
Then you say
> It is used as a surrogate key.
All keys are surrogates.
By anyones cound, a natural key must be a key. You state that all keys
are surrogates, hence, due to our old friend the transitive property, all
natural keys are surrogates. .
Natural keys are a subset of surrogate keys. Where have I suggested
anything different? Had I stated that natural keys and surrogate keys are the same
thing, I would have had to state that surrogate keys are a subset of
natural keys as well as vice versa. I don't recall ever writing any such thing.
You said that all keys are surrogate keys. So KEY = SURROGATE KEY. A
NATURAL KEY is not a subset of a key, it is a key. So SURROGATE KEYS are a
subset of KEYS, and NATURAL KEYS are a subset of KEYS, and there is some
intersection of SURROGATES and NATURAL KEYS where neither is a subset of the
other.
Hence a Surrogate Key is a key, and a Natural key is a key, but a key can be
a member of the set of natural keys, or the set of surrogate keys (and since
I didn't say XOR, it can be both.)
A surrogate key is a unique identifying attribute that is not derived from any other data in the database and whose only significance is to act
as an identifying attribute.
By any other data, it means that the value is not based on the existence
of any other data. Otherwise all normalized data (one single source) is by
your definition surrogate data.
To contradict my statement, all you have to do is identify one concrete
example of a useful key that is not a surrogate for anything.
DNA was a good example. A car's VIN number is (though it is a smart key
made up of many keys) not a surrogate. It is a massively compound key that
includes many real values (year produced, style, engine, and the sequence of
production etc.) While there are surrogate keys for each of the different
parts, the key itself is not a surrogate, but a description of the thing it
is representing.
> You name is part of what makes you you, because everyone has a name.
Your statement shows a general lack of imagination. Not every infant
is named at the moment of birth, and my name is not a part of me. It is
external to me, and I do not change when my name changes.
We are talking about modeling reality in relational database, not
reality in and of itself.
You are clutching at straws. Do you lack sufficient intellectual honesty
to cede a point when appropriate?
No, you apparently lack intelluctual capability to discuss the concept of a
key strictly in database terms, not the real world. By your definition,
every adjective is a surrogate key for the thing it is describing. Is blue
a surrogate key for the color blue?
If it is a matter of taste, then I don't mind anyhow. I like
hearing others
> opinions, and as to why my ideas are wrong if they are (and some
are.)
It is not a matter of taste but a matter of education. By very
objective criteria, hiding the logical identifier from users is just plain
stupid. A user must have access to the logical identifier to properly and to correctly express queries.
Two reasons this is wrong. One, most users do not do any direct
querying into an OLTP database directly.
All users of the dbms use the dbms. I don't care about users of other
things who do not use the dbms.
> I have these discussions so I can improve my opinions that I
> have carefully crafted over 11 years, and that I frequently give to
others.
Some people frequently give others syphyllus, but I would not
congratulate them for the deed. I suggest you get more out of the gift than the others do.
The only think I can think to respond here is "You are a ninny." Though
that is possibly a bit sophisticated a response to such a preposterously
banal comment from someone who has such high regards for his own
knowledge.
By sophisticated do you mean? Impure? Adulterated? Having used sophistry?
Lacking natural simplicity? Not genuine? Rendered worthless by admixture?
Damaged? Perverted? Debased? Corrupted? Vitiated?
http://dictionary.reference.com/search?q=sophisticated
Sophisticated has so many meanings its use is often nebulous. Regardless
of the meaning you intend, your sophistry underwhelms me.
This is the most idiotic thing I have ever heard. If you do not know the
common use of the word sophistication, then you need to use that dictionary.
Strange that you skip the obvious common usage by most of the English
speaking folks. "Having acquired worldly knowledge or refinement; lacking
natural simplicity or naiveté"
Meaning you argue your point like a five year old with a high IQ. Attacking
things that are not the issue to distract from a lack of refinement (and I
am not indicating that you should be purified, I mean "cultivation, as in
manners or taste," specifically in manners)
--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"Bob Badour" <bb*****@golden.net> wrote in message
news:Pd********************@golden.net...
<drivel snipped>
OK Bob, I tried...I give up you win. Troll away. I am done.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com
"Louis Davidson" <dr***************@hotmail.com> wrote in message
news:#B*************@tk2msftngp13.phx.gbl...
"Bob Badour" <bb*****@golden.net> wrote in message
news:cp********************@golden.net... "Louis Davidson" <dr***************@hotmail.com> wrote in message
news:Oo**************@tk2msftngp13.phx.gbl... You are starting to get on my nerves with your claims of having
super-preciseness that no one else has. You state:
> > Well, then apparently you are so good at English, and less good at being > > precise. If natural keys and surrogate keys were in fact the same
thing,
> > then why would we have multiple terms for these things?
>
> I don't recall saying they are the same thing. I recall saying that one is a
> subset of the other. Perhaps, if you had better grasp of written
english, > you would have observed that the first time. I see nothing imprecise
about > what I said.
Then you say
> > It is used as a surrogate key.
>
> All keys are surrogates.
By anyones cound, a natural key must be a key. You state that all
keys are surrogates, hence, due to our old friend the transitive property, all
natural keys are surrogates. .
Natural keys are a subset of surrogate keys. Where have I suggested
anything different? Had I stated that natural keys and surrogate keys are the
same thing, I would have had to state that surrogate keys are a subset of
natural keys as well as vice versa. I don't recall ever writing any such thing.
You said that all keys are surrogate keys. So KEY = SURROGATE KEY. A
NATURAL KEY is not a subset of a key, it is a key. So SURROGATE KEYS are a
subset of KEYS, and NATURAL KEYS are a subset of KEYS, and there is some
intersection of SURROGATES and NATURAL KEYS where neither is a subset of
the other.
I never claimed that surrogate keys are a proper subset of keys--quite the
opposite. You have yet to demonstrate that surrogate keys are a proper
subset of keys. All candidate keys are surrogates, which makes candidate
keys a subset of surrogates. They are in fact a proper subset of surrogates.
> A surrogate key is a unique identifying attribute that is not
derived from > any other data in the database and whose only significance is to act as an > identifying attribute.
By any other data, it means that the value is not based on the
existence of any other data. Otherwise all normalized data (one single source) is
by your definition surrogate data.
To contradict my statement, all you have to do is identify one concrete
example of a useful key that is not a surrogate for anything.
DNA was a good example.
If it was such a good example, how was I able to shred the example so
easily? We have identical twins, chimeras, virus and mutation that make DNA
inappropriate to use as a candidate key. Quite simply if fails to provide
the most basic requirements of a candidate key: namely logical identity.
A car's VIN number is (though it is a smart key
made up of many keys) not a surrogate.
A VIN is not a car. It is a surrogate invented by the automobile industry
and assigned by the manufacturer.
While there are surrogate keys for each of the different
parts, the key itself is not a surrogate, but a description of the thing
it is representing.
In other words, you agree the VIN is not the thing. It is a surrogate for
the thing that describes the thing. You have contradicted yourself.
> > You name is part of what makes you you, because everyone has a
name. >
> Your statement shows a general lack of imagination. Not every infant is > named at the moment of birth, and my name is not a part of me. It is
> external to me, and I do not change when my name changes.
>
We are talking about modeling reality in relational database, not reality in and of itself.
You are clutching at straws. Do you lack sufficient intellectual honesty
to cede a point when appropriate?
No, you apparently lack intelluctual capability to discuss the concept of
a key strictly in database terms, not the real world.
I agree. I completely lack intelluctual capability--including the
intelluctual capability to ignore the real world in support of absurd
notions. I do, however, have the intellectual honesty to cede a point when
appropriate--as seldom as it is appropriate.
Is blue
a surrogate key for the color blue?
As I said earlier, the representation "blue" is a surrogate for the value.
Actually, it is a surrogate for several different values in different
contexts. In some contexts, it is a surrogate for a concept encompassing a
range of visible wavelengths or combinations of wavelengths at various
amplitudes perceived by the human visual processing system as a wavelength
in the previous range of visible wavelengths. In another context, it is a
surrogate for a concept encompassing a range of depressed or melancholic
emotions. In another context, it is a surrogate for a concept of sexual
content suitable for adult audiences.
> > If it is a matter of taste, then I don't mind anyhow. I like hearing > others
> > opinions, and as to why my ideas are wrong if they are (and some are.) >
> It is not a matter of taste but a matter of education. By very objective > criteria, hiding the logical identifier from users is just plain stupid. A > user must have access to the logical identifier to properly and to
correctly
> express queries.
Two reasons this is wrong. One, most users do not do any direct
querying into an OLTP database directly.
All users of the dbms use the dbms. I don't care about users of other
things who do not use the dbms.
> > I have these discussions so I can improve my opinions that I
> > have carefully crafted over 11 years, and that I frequently give
to > others.
>
> Some people frequently give others syphyllus, but I would not
congratulate > them for the deed. I suggest you get more out of the gift than the
others > do.
>
The only think I can think to respond here is "You are a ninny."
Though that is possibly a bit sophisticated a response to such a
preposterously banal comment from someone who has such high regards for his own
knowledge.
By sophisticated do you mean? Impure? Adulterated? Having used
sophistry? Lacking natural simplicity? Not genuine? Rendered worthless by
admixture? Damaged? Perverted? Debased? Corrupted? Vitiated?
http://dictionary.reference.com/search?q=sophisticated
Sophisticated has so many meanings its use is often nebulous. Regardless
of the meaning you intend, your sophistry underwhelms me.
This is the most idiotic thing I have ever heard. If you do not know the
common use of the word sophistication, then you need to use that
dictionary.
The whole point is I do know the meanings of sophistication: unnaturalness,
impurity, complexity and sophistry. Which of them did you mean?
I suggest, if you make an effort to understand the words you see and use,
you will improve your ability to comprehend relatively simple english.
Otherwise, you simply refuse to communicate.
Strange that you skip the obvious common usage by most of the English
speaking folks. "Having acquired worldly knowledge or refinement; lacking
natural simplicity or naiveté"
Strange that you didn't notice I included "lacking natural simplicity."
Perhaps, if you also make greater effort to be observant, you will further
improve your ability to comprehend relatively simple written english. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Phil W |
last post by:
Hi all,
Am having a bit of trouble with the @@identity field - I probably just have
that friday feeling and am missing off something obvious, but the below code
brings back am empty identity...
|
by: Mark |
last post by:
Hi,
How to add a foreign key constraint using the SQL server 2000
enterprise manager?
Not by SQL.
thanks
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
