id vs. pk/fk prefix

[Natan]

Hi.. I would like an opinion about this silly thing:

In my office, we use PK/FK prefixes to indicate Primary/Foreign keys.
Before working there, I aways used "ID" to indicate these columns.

so, instead of having a User table with pkUser and all other tables
having fkUser, I like the User table to have an user_id and other tables
have the user_id field as well. If i'm in table "messages" and have a
"user_id" field, of course it's a foreign key because the primary would
be the "message_id" field.

I think this second scheme is more obvious for the same reason that I
name a field "Name" instead of "strName", or "active" instead of
"bitActive"... for me it just doesn't matter the type of data or the
constraints it has in the name of the field. If I need to use a table a
never worked before, I'll just check it with Query Analyser or any other
tool in another database, and it's all there, constraints and data types.

My friends agree about "strName" being stupid, but "pk/fk" is stuck in
their minds as if it would bring the company to the chaos if we use
"id". They tell me it's "microsoft recommendation".. Well, sql server
internal and example tables use "id" for "ids" and it seems to work okay =)

I would like to know the opinion of other users about this, and what are
the pros/cons about "ids" and "pks".

Thanks =)

[Jaxon]

I agree with you but is it worth the fight ?


Greg Jackson
PDX, Oregon

[Natan]

I'm not fighting with anyone. I would just like to know if it's a major
practice, or what most of you use...

By the way, yes, i think it's worth the fight.

Post by Jaxon
I agree with you but is it worth the fight ?

[Jacco Schalkwijk]

I don't think using pk/fk in column names is a good idea, for the same
reason as including the datatype in the column name isn't a good idea. It
includes information in the name of an object that is not strictly tied to
it. What happens if your table structure changes and you have different
PK/Fks? Are your colleagues going to change all the column names?

I can advise you to search the archives of this group on Google for comments
on this subject by Joe Celko. He can put that far more eloquently than I
can.

Jacco Schalkwijk
SQL Server MVP

Post by Natan
I'm not fighting with anyone. I would just like to know if it's a major
practice, or what most of you use...
By the way, yes, i think it's worth the fight.

Post by Jaxon
I agree with you but is it worth the fight ?

[Jaxon]

agreed.

trying to include schematic info in the name is poor design.


GAJ

[Jaxon]

it is industry standard NOT to include that info in the names.

What if you have a 1 to 1 relationship and the PKey in a table is ALSO a
FKEY to another table ? is the name then PKFK_MyTable ?

How about recursive Relationships ?

etc etc etc

these naming conventions seem to come from the desktop world (aka....MS
Access developers)

I see the same thing with hungarian notation on table names and column
names....

aka.....tblCustomer and strCustName etc etc etc

YUCK


Cheers

GAJ

Post by Natan
I'm not fighting with anyone. I would just like to know if it's a major
practice, or what most of you use...
By the way, yes, i think it's worth the fight.

Post by Jaxon
I agree with you but is it worth the fight ?