damaged people dot com

Ok... I'm a geek. I work in a geeky place and am surrounded by geeks.

The Network Engineer for our department (yes, we have our own NE) came over to me a few minutes ago because he was having problems with a NIC (network interface card).

I had to point out that if he'd looked at the card, there were three hints as to why it doesn't work:

1) It's bezle is form factor, not ATX.
2) It's microchannel and not PCI (2 notches vs. 1, pins too far forward)
3) It's for a friggin' IBM PS/2, not a PC!!

To top it off... our IT department gave him that card.

Shouldn't a Network Engineer know this? Isn't this like having your DBA come over to you because he cant figure out how to make a self incrementing primary key in a table (oh wait... that's happened here too).

*shudder*

I guess that's why I've survived 5 layoffs.

heh, I'll bet someone in IT busted up after he walked away with the card.

hehhh hehhh "Microchannel"

Um... I had to point out to thus said person why it wouldn't work.

He then "remembered" what it was.

And to think I recently tossed out a couple of old microchannel expansion boards at home. I never knew I could have had so much fun with a person of higher education (?)

bio wrote:come over to you because he cant figure out how to make a self incrementing primary key in a table

HERATIC!!!!

I don't allow surrogating except in the most extreme situation. I never allow surrogating on DB2, on the same subject, I don't allow bit fields either.

Bad developer, no doughnut!

...don't make me get Celko on your lilly white a**!

It's extremely handy when developing web based applications via ColdFusion when you pass a user ID around as a session variable. Each user gets a distinct user ID.

It's the rosetta stone of most of my apps.

Oh! Tell me it isn't so!

I'm ok with an autonumber attribute on a table for systemic access to data (alternate key) but not as a key. From Joe Celko via the newsgroups:
" BY DEFINITION, an exposed physical locator like
GUIDs, IDENTITY, ROWID and other proprietary non-relational things
cannot be a key. A key is made of attributes, not the physical state
of a piece of hardware at insertion time. That is how a record number
in a magnetic tape file works.

If you really believe that these things are keys, would you believe
that there is a 17-letter hebrew number that God put on the bottom of
everything in creation? This is superior to using IDENTITY because you
can get a rabbi to read it to you.

Sounds pretty silly, doesn't it? Why? Because such a number does not
exist in the real world. See the point? Instead of mimicking tape
files, you might want to learn how to build a real data model with real
keys, so you can keep logical and physical models apart."

Use the identity as an alternate key if you must, but using it as the primary key is the most often committed integrity error in the business, besides, it absolutely destroys performance.

Ok... perhaps I'm not being clear here.

I need a disctinct number for every insert into the user table. This number is the ID by which I tie it all together (inserting this number into this table and that table as to identify what data belongs to what user).

By calling it a primary key (which is a MS Access term), I simply mean that it's an auto incrementing number that's unique in one table for each row.

Better? Same? Worse?

bad:
create table foo (
fooID int identity (1,1) primary key,
fooFirstname varchar(12),
fooLastname varchar(12),
fooSSN char(11) not null )


good:
create table foo (
fooID int identity (1,1),
fooFirstname varchar(12),
fooLastname varchar(12),
fooSSN char(11) not null primary key )

It's a relational thing, not a programming thing, you may not understand

It's a relational thing, not a programming thing, you may not understand

Ok, that might have been a bit rude.

Here's the skinny, a primary key isn't an access term, it's a relational term and can be represented algebraically so the theory is useable across DBMSs. It should be chosen as it is externally verifiable AND naturally unique. In short, it should have meaning.

With the recent explosion of easy to use database design tools, more and more people are designing database schemas with no foundation in relational algebra (some without knowledge of predicate logic). Many authors and software publishers are suffering the same fate and the fad of surrogating a key for an auto number identity is catching on throughout the industry. This is VERY dangerous as generally the natural key is recorded, but not constrained, so in the previous example, we could have two different tuples in the relvar foo with the exact same attributes. When in fact (the real world) there is only one person with that social security number.

To add insult to injury, once a surrogate is chosen as the primary key, all child tables will use that as their foreign key. This is a performance nightmare. If the natural key had been used, it would also have been used in the child tables so finding all the children of a given parent could have taken a query against one table with no costly joins. Since we surrogated it, a join is necessary causing undue stress on the DBMS.

Hugh Darwin and Chris Date go into this much more eloquently in "The Third Manifesto" which has been argued here but later, and quite logically rebutted here. The dangers of identity are also defined quite well in SQL for Smarties by Joe Celko.

Fabian Pascal also maintains http://www.dbdebunk.com, there's more valuable information on the subject of data management there than most universities put in their computer science programs.

If this horse isn't dead yet, and anyone is still confused, please let me know. I'm afraid these are the issues I deal with all day every day!

ROh

parent? child? foo? RATIONAL algebra? natural keys?

WTF?

I guess Asa and I should start having conversations about hardcore electronics terms...that'll get you back.

Clearly you know of what you speak, but you confused the shit out of me...not asking you to explain, you'd have no hope of making me understand!

Rocketdork wrote:parent? child? foo? RATIONAL algebra? natural keys?

WTF?

I guess Asa and I should start having conversations about hardcore electronics terms...that'll get you back.

Could be why the keep me in the corner and the general populace only come by when the HAVE to.

But don't get too upity about electronics terms, I bet I could at least keep up!

ROh

Yea, besides, I -do- have programming experience. I -am- able to digest the bulk of the rant, I chose to make a statement though, that it -was- getting a bit deep, and even over my head, or rather, over where -I- wanted to go.


Perhaps we need a new Emoticon, one with some DEEP WADERS on.