Guid vs Identity

[Daniel Morgan]

Re: Guid vs Identity

Erland Sommarskog wrote:[color=blue]
> Daniel Morgan (damorgan@x.was hington.edu) writes:
>[color=green]
>>Far too many people seem to be of the opinion that
>>
>>1. I have a problem
>>2. Surrogate keys are a solution
>>3. Therefore I need surrogate keys[/color]
>
>
> But that does not mean that anyone who is asking about autonumber values
> is one of those people.[/color]

No it does not guarantee it. But if you've been following this usenet
group for more than a few days you know this is a band that has a lot
of percussion and very little melody.
[color=blue][color=green][color=darkred]
>>>Neither you, nor I nor Celko knows what Ilija's business requirements
>>>are.[/color]
>>
>>What makes you think Ilija does either? ;-)[/color]
>
> You can bet your ass that he knows more about it than we do. We don't
> even know his business domain.[/color]

I not only woudn't bet my ass I wouldn't bet yours. To assume that
someone in our business knows what they are doing flies in the face
of a lot of evidence.
[color=blue][color=green]
>>I'm not knocking humility but you are confusing quality of advice with
>>how it was given.[/color]
>
> How it was given, indeed has a lot do it. Had Celko posted something
> like "In general autonumber or similar are not good solutions for
> database implementation" , and continued to explain in friendly voice,
> and in away so that even a person with a low experience of database
> implemetnation could get an understanding of what he was talking about,
> I would not bother. If you want an example who is very good to express
> himself in such away, I encourse you to search Google groups for posts
> by BP Margolin who unfortunately does not post here any more.[/color]

This world is populated by adults. Get used to it. Either way ... I may
not have chosen to communicate Celko's thoughts with Celko's words ...
but his advice was more likely to be accurate than any other that could
have been given.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Erland Sommarskog]

Re: Guid vs Identity

Daniel Morgan (damorgan@x.was hington.edu) writes:[color=blue]
> Erland Sommarskog wrote:[color=green]
>> But that does not mean that anyone who is asking about autonumber values
>> is one of those people.[/color]
>
> No it does not guarantee it. But if you've been following this usenet
> group for more than a few days you know this is a band that has a lot
> of percussion and very little melody.[/color]

Not only have I followed this newsgroup for quite some time, and I
have also worked with an application for quite some time, and I have
come to the realisation that natural keys out of the real world are
rare creature. Have you ever tried to model financial instruments?

(Hint: the situation when a natural key breaks down is when your users
insist on adding something to the system which is not in the domain of
that natural key, but yet is in the domain of the users' business.)
[color=blue]
> I not only woudn't bet my ass I wouldn't bet yours. To assume that
> someone in our business knows what they are doing flies in the face
> of a lot of evidence.[/color]

Ilija may or may not be using is uniqueidentifie r for a good reason.
But he knows what system he is working with, and you and I and Celko
don't. If you still think that you know better than him, that gives
more ideas about your ego than your professional knowledge.
[color=blue]
> This world is populated by adults. Get used to it.[/color]

If Celko was only pulling the legs of me and other SQL Server MVPs
I could live with it. Now the victims for his meaningless tirades
are innocent people who deserve a better start in the SQL Server
world.

And if you think this is "adult" behaviour, then I am not coming over
to your playground.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Daniel Morgan]

Re: Guid vs Identity

Erland Sommarskog wrote:
[color=blue]
> Daniel Morgan (damorgan@x.was hington.edu) writes:
>[color=green]
>>Erland Sommarskog wrote:
>>[color=darkred]
>>>But that does not mean that anyone who is asking about autonumber values
>>>is one of those people.[/color]
>>
>>No it does not guarantee it. But if you've been following this usenet
>>group for more than a few days you know this is a band that has a lot
>>of percussion and very little melody.[/color]
>
>
> Not only have I followed this newsgroup for quite some time, and I
> have also worked with an application for quite some time, and I have
> come to the realisation that natural keys out of the real world are
> rare creature. Have you ever tried to model financial instruments?[/color]

After 35 years in this industry I've done it more than a few times. And,
as I said before, while there are times when surrogate keys are the
correct solution ... that does not mean that every problem is a nail
and requires the use of hammer: Most don't. But thank you for
intentionally selecting an exception.
[color=blue]
> (Hint: the situation when a natural key breaks down is when your users
> insist on adding something to the system which is not in the domain of
> that natural key, but yet is in the domain of the users' business.)[/color]

Users don't insist on anything that breaks a system down in a
professional run IT shop. If you've faced that problem you might wish
to consider that the problem is in your IT department.

How is it that Date, Codd, Celko, and many others far more highly
qualified to discuss relational databases than either of us have
managed to not run into this issue as a show-stopper? Do you know
wish to claim you know more than the ASCII committee? How about the
ISO?

You can posture all you wish ... but the vast majority of the time
professionals in our industry will find your advice the quick easy
way out that more often than not demonstrates a lack of formal
education and training.
[color=blue][color=green]
>>I not only woudn't bet my ass I wouldn't bet yours. To assume that
>>someone in our business knows what they are doing flies in the face
>>of a lot of evidence.[/color]
>
> Ilija may or may not be using is uniqueidentifie r for a good reason.[/color]

And he may not be. But of course that doesn't stop you from jumping in
and defending the quick fix where serious thought might be required.
[color=blue][color=green]
>>This world is populated by adults. Get used to it.[/color]
>
> If Celko was only pulling the legs of me and other SQL Server MVPs
> I could live with it. Now the victims for his meaningless tirades
> are innocent people who deserve a better start in the SQL Server
> world.[/color]

Innocent? No one that takes a job and cashes paychecks for doing a
specific job is "innocent". Try treating what we do as a profession
rather than a hobby.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[DCM Fan]

Re: Guid vs Identity

This thread has been an interesting read, to say the least, watching a few
highly-respected Usenet folks "duke it out" on multiple principles. (Notably
Surrogate Keys and Usenet Focus)

Although I'm not in the same league as the aforementioned respectables, I feel
compelled to put in my 2 cents anyway!

I'm familiar with Chris Date and Celko in that I've read their stuff, and I
"seek" their columns with internet searches every now-and-then. I certainly
respect their work. Erland has also helped me in the past.

I frequent Date's and Fabian Pascal's website, dbdebunk.com, admittedly more
for its dry entertainment value, but the site does give ammo to those of us who
try to "do things the right way" in database design for business. (Their
anti-XML stuff is priceless!)

I don't think there's any argument that the "right way" from an ideal design
standpoint is to forget about surrogate keys altogether. (Is there ANY room for
surrogates???)

I gotta tell ya, though, that can be painful. I've got a table with 5 fields in
its primary key, and it has a child (foreign) table with 7 fields in its
primary key (The 5 from the parent, and 2 others.) Yes, I'm actually using a
5-field Foreign Key constraint to hold them together!

I learned databases originally through MS Access, so I would normally have just
said "screw it" and thrown an AutoIncrement in there, and related it using
that, but I have to admit that I was "determined to do this database the right
way" after having been educated by the likes of Celko and Date over the last
few years.

I sometimes regret that decision. It turns out that the "right way" has bogged
down the development time dramatically, because every time I need to reference
those tables, I have to write SQL Statements with ugly joins, long WHERE
clauses, and I have to deal with 5 or 7 fields all the time...passing those
values to-and-from application to server.

If I had used Autoincrement on the parent, related one-many on the child with
that field, development time would have been drastically reduced, and I could
still have used unique indexes judiciously to preserve integrity. I'm really
not sure what I've gained, however, except the right to say "I built it the
right way."

My point is this: Although I strive to do everything "the right way," reality
can interfere with the abstract ideal. I get the impression that the
"Idealists" are so far up the Ivory Tower that they have trouble dealing with
the real world, below the clouds. (Or they are SO Intelligent, Idealistic and
Abstract, they just refuse to--or literally CANNOT--acknowledge reality.)

I'm not being flip. I'm dead serious.

Case in point is this thread. The original post only wanted to know about the
pros and cons of GUID vs Identity. Hell, even the post was TITLED as such!
Erland offered awesome advice toward that post.

But the Idealists--a righteous group at that--couldn't let it go!
(Understandably , too, since it's the nature of righteousness!) It didn't matter
to them that the original post had no questions about when/where/how/why to USE
GUID and/or Identity. It didn't occur to them that the original poster may have
already thought through the ramifications (or inherited the application).

Instead, their retort was quite predictable, questioning mere existence of
GUID/Identity in the schema, while at the same time using subtle "put-downs"
against the implementer.

Personally, I was glad Celko posted what he did. Being righteous about
GUID/Identity is a good thing. I was also glad Erland posted his "cut the crap"
post back at Celko, because he brought to light the 'meaning' of Usenet and the
context of the original question--that is, he brought the post back to the real
world.

Although I consider myself a 'believer' in the relational model, I probably
won't use real keys ever again if they go beyond about 3 fields, maybe 4 at the
end of a one-many-many-many chain! I thank God he gave me the intelligence to
comprehend the Relational Model (well, Set Theory and Logic anyway), but I'm
more grateful that I can use that knowledge in the real world!

[Erland Sommarskog]

Re: Guid vs Identity

Daniel Morgan (damorgan@x.was hington.edu) writes:[color=blue]
> After 35 years in this industry I've done it more than a few times. And,
> as I said before, while there are times when surrogate keys are the
> correct solution ... that does not mean that every problem is a nail
> and requires the use of hammer: Most don't. But thank you for
> intentionally selecting an exception.[/color]

I didn't take financial instruments out of the blue. This is the core
of the business domain I work with.
[color=blue][color=green]
>> (Hint: the situation when a natural key breaks down is when your users
>> insist on adding something to the system which is not in the domain of
>> that natural key, but yet is in the domain of the users' business.)[/color]
>
> Users don't insist on anything that breaks a system down in a
> professional run IT shop. If you've faced that problem you might wish
> to consider that the problem is in your IT department.[/color]

We're an ISV, and there are changes in our customer's business - and
this is an ever-changing world - they can not always wait for our
next release, but have to work around with what they have. Or it
might just be that this particular part of the business is too marginal
to warrant the price tag that we would offer them.
[color=blue]
> How is it that Date, Codd, Celko, and many others far more highly
> qualified to discuss relational databases than either of us have
> managed to not run into this issue as a show-stopper?[/color]

Maybe because they have not worked much with real-world system and
gotten dirt under their nails? Nice talk about "the problem is in
your IT department" is alwyas easy to say from an armchair.
[color=blue][color=green]
>> Ilija may or may not be using is uniqueidentifie r for a good reason.[/color]
>
> And he may not be. But of course that doesn't stop you from jumping in
> and defending the quick fix where serious thought might be required.[/color]

No, I am not defending "the quick fix". Hell, none of us know if it is
a quick fix or not. You may have 35 years of experience in industry, but
I find that hard to belive. Had you had that experience, you would have
learnt that sometimes you are right, and sometimes you are wrong. And
you should have learnt to not talk about something you don't know about.
[color=blue][color=green]
>> If Celko was only pulling the legs of me and other SQL Server MVPs
>> I could live with it. Now the victims for his meaningless tirades
>> are innocent people who deserve a better start in the SQL Server
>> world.[/color]
>
> Innocent? No one that takes a job and cashes paychecks for doing a
> specific job is "innocent". Try treating what we do as a profession
> rather than a hobby.[/color]

He is innocent in the sense that just because he gets a paycheck he has
no reason to accept being slammed by nipwits who don't know anything
about his work.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Daniel Morgan]

Re: Guid vs Identity

Erland Sommarskog wrote:
[color=blue]
> Daniel Morgan (damorgan@x.was hington.edu) writes:
>[color=green]
>>After 35 years in this industry I've done it more than a few times. And,
>>as I said before, while there are times when surrogate keys are the
>>correct solution ... that does not mean that every problem is a nail
>>and requires the use of hammer: Most don't. But thank you for
>>intentional ly selecting an exception.[/color]
>
>
> I didn't take financial instruments out of the blue. This is the core
> of the business domain I work with.[/color]

That may be true. But to either assume everyone else is doing the same.
Or that a specific person is doing so is a huge stretch. Rules have
exceptions. But surrogate keys are the result of rejecting the preferred
approach ... not the initial approach except for the lazy.
[color=blue][color=green][color=darkred]
>>>(Hint: the situation when a natural key breaks down is when your users
>>>insist on adding something to the system which is not in the domain of
>>>that natural key, but yet is in the domain of the users' business.)[/color]
>>
>>Users don't insist on anything that breaks a system down in a
>>professiona l run IT shop. If you've faced that problem you might wish
>>to consider that the problem is in your IT department.[/color]
>
> We're an ISV, and there are changes in our customer's business - and
> this is an ever-changing world - they can not always wait for our
> next release, but have to work around with what they have. Or it
> might just be that this particular part of the business is too marginal
> to warrant the price tag that we would offer them.[/color]

That may well be. And don't get me started on SAP, PeopleSoft or the
worst of the worst ... Siebel.
[color=blue][color=green]
>>How is it that Date, Codd, Celko, and many others far more highly
>>qualified to discuss relational databases than either of us have
>>managed to not run into this issue as a show-stopper?[/color]
>
> Maybe because they have not worked much with real-world system and
> gotten dirt under their nails? Nice talk about "the problem is in
> your IT department" is alwyas easy to say from an armchair.[/color]

Maybe? Perhaps you should learn more about them. Your assumption is not
valid.
[color=blue][color=green][color=darkred]
>>>Ilija may or may not be using is uniqueidentifie r for a good reason.[/color]
>>
>>And he may not be. But of course that doesn't stop you from jumping in
>>and defending the quick fix where serious thought might be required.[/color]
>
> No, I am not defending "the quick fix". Hell, none of us know if it is
> a quick fix or not. You may have 35 years of experience in industry, but
> I find that hard to belive. Had you had that experience, you would have
> learnt that sometimes you are right, and sometimes you are wrong. And
> you should have learnt to not talk about something you don't know about.[/color]

My first work in this industry was Fortran IV with punchcards working
on an IBM 370-145 in 1969: You do the math.

What I've learned is that the vast majority of the time the best advice
is what Celko gave. And I clearly stated that his advice was helpful.
Not perfect ... helpful. And should not have received the disrespectful
response you gave it.
[color=blue][color=green][color=darkred]
>>>If Celko was only pulling the legs of me and other SQL Server MVPs
>>>I could live with it. Now the victims for his meaningless tirades
>>>are innocent people who deserve a better start in the SQL Server
>>>world.[/color]
>>
>>Innocent? No one that takes a job and cashes paychecks for doing a
>>specific job is "innocent". Try treating what we do as a profession
>>rather than a hobby.[/color]
>[/color]
[color=blue]
> He is innocent in the sense that just because he gets a paycheck he has
> no reason to accept being slammed by nipwits who don't know anything
> about his work.[/color]

Calling people with decades of expertise nitwits because you don't
share their opinion says much about you. My guess, and it is only a
guess, is that you've never taken a university level academic course
in relational theory or practice. What you've learned you've learned
on the job. And that practice is the reason why so many jobs are
being off-shored to people who, yes work for less, but also know what
they are doing.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[William Cleveland]

Re: Guid vs Identity

--CELKO-- wrote:[color=blue][color=green][color=darkred]
>>>Guid or Autonumber .. as a primary key? <<[/color][/color]
>
>
> Did you ever consider not using either and find a REAL key? All you
> are doing is using PHYSICAL locators[/color]

Except, of course, that the rows may or may not be actually
physically located that way. Given that database tables are
mostly built with B-Trees, they probably aren't located that
way.

Bill

[xAvailx]

Re: Guid vs Identity

>>I encourse you to search Google groups for posts
by BP Margolin who unfortunately does not post here any more.<<

What did happen to him? Does he post anywhere else anymore?

Thx,

BZ

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns9515DB F54E618Yazorman @127.0.0.1>...[color=blue]
> Daniel Morgan (damorgan@x.was hington.edu) writes:[color=green]
> > Far too many people seem to be of the opinion that
> >
> > 1. I have a problem
> > 2. Surrogate keys are a solution
> > 3. Therefore I need surrogate keys[/color]
>
> But that does not mean that anyone who is asking about autonumber values
> is one of those people.
>[color=green][color=darkred]
> >> Neither you, nor I nor Celko knows what Ilija's business requirements
> >> are.[/color]
> >
> > What makes you think Ilija does either? ;-)[/color]
>
> You can bet your ass that he knows more about it than we do. We don't
> even know his business domain.
>[color=green]
> > I'm not knocking humility but you are confusing quality of advice with
> > how it was given.[/color]
>
> How it was given, indeed has a lot do it. Had Celko posted something
> like "In general autonumber or similar are not good solutions for
> database implementation" , and continued to explain in friendly voice,
> and in away so that even a person with a low experience of database
> implemetnation could get an understanding of what he was talking about,
> I would not bother. If you want an example who is very good to express
> himself in such away, I encourse you to search Google groups for posts
> by BP Margolin who unfortunately does not post here any more.[/color]

[Erland Sommarskog]

Re: Guid vs Identity

xAvailx (bjzamora@hotma il.com) writes:[color=blue][color=green][color=darkred]
>>>I encourse you to search Google groups for posts[/color][/color]
> by BP Margolin who unfortunately does not post here any more.<<
>
> What did happen to him? Does he post anywhere else anymore?[/color]

I have no idea of his whereabouts. If you see him somewhere, please
say hello from me!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Guid vs Identity

Daniel Morgan (damorgan@x.was hington.edu) writes:[color=blue]
> That may be true. But to either assume everyone else is doing the same.
> Or that a specific person is doing so is a huge stretch. Rules have
> exceptions. But surrogate keys are the result of rejecting the preferred
> approach ... not the initial approach except for the lazy.[/color]

It's funny, though, that when I take something from my own business that
you immediately admit that this is an exception. I'll give you another
core item in our application which appears equally difficult to model
with natural keys: customers.
[color=blue]
> What I've learned is that the vast majority of the time the best advice
> is what Celko gave. And I clearly stated that his advice was helpful.
> Not perfect ... helpful. And should not have received the disrespectful
> response you gave it.[/color]

Let's see, Celko gives a very disrespectful response to Ilija, and
when I criticize that, you complain that I am being disrespectful?
[color=blue]
> Calling people with decades of expertise nitwits because you don't
> share their opinion says much about you.[/color]

You are nipwits becase you are slamming someone (or defends the
slamming) without nothing zilch about what he is doing. And just because
you can call yourself an expert in relational databases does not
defend that. You are not an expert in Ilija's business, but complete
ignorants.
[color=blue]
> My first work in this industry was Fortran IV with punchcards working
> on an IBM 370-145 in 1969: You do the math.[/color]

This is not a discussion about math. This is a discussion about social
skills. Of which one would expect that your 35 years in industry
should have helped you to acquire some.

You see: that is the core of the discussion *How* it is being said.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp