Unique Column Names

[John Smith]

Is anyone out there interested in the Lyszinski / Reddick Naming Convention?

I have used a slightly modified version of this convention successfully in
many projects, and I have noticed that it is extremely useful in Reverse
Engineering projects and OLAP projects. That is because the convention
requires unique names for every column in any database.

For example, you would never use CLIENT_ID as a field name, since it
violates uniqueness when used as a foreign key, making reverse engineering
and XML parsing difficult.

A better name for CLIENT_ID is:

intClientPK

and

intClientFK

Still, many tools tend to automatically force their own naming. ERWin, for
example, forces duplicate names for FK / PK pairs across tables. I find this
extremely annoying. It's like your wedding organizer telling you what colour
your underwear has to be!

John Smith.

 

[John Smith]

Another thing ...

Underscores have to be the WORST character in a column name.

I would like to start an "UNDERSCORES SUCK" movement.

John Smith

 

[John Smith]

And this "ID" suffix thing? Who the HELL invented that for a SUFFIX?

Certainly CODD is not so stupid.

And who decided to concat "_FK" to the end of names of relationships?

What kind of IDIOT would do that?

A Primary Key COLUMN is NOT a Primary Key!

OH NO!

A Primary Key is the INSTANCE OF DATA stored in a PRIMARY KEY COLUMN's
INTERSECTION with the Row.

So if you say: "This column is a primary key", you don't really mean that.

You should say: "This PRIMARY KEY COLUMN contains instances of data, each
one of which uniquely identifies the entire row."

Subtle, yes, but absolutely imperative when discussing naming conventions.

NEVER EVER USE "ID" anywhere in a data model.

A COLUMN name should reflect it's purpose in a JOIN.

If the KEY COLUMN is FOREIGN, don't call it, for example, CUSTOMER_ID!

It's not an ID if it's in the ORDER table! No, it's a FOREIGN KEY COLUMN!
The rows contain data elements which are FOREIGN KEYS, not PRIMARY KEYS!

This is because a FOREIGN KEY allows DUPLICATES, but a PRIMARY KEY DOES NOT!

Make sure you realize this, and use the correct English!

Even in a ONE TO ONE relationship, the column name should be different than
it's concomitant parent. Otherwise, you are going to get caught when it's
time to OLAP your data or reverse engineer your tables.

You see, TOOLS like ERWin actually CORRUPT the thinking process by forcing
naming that is not grammatically correct.

A JOIN between one column and another can only be resolved if the INSTANCES
of DATA are exactly the same on either side of the join. A JOIN, then, is
only resolved ONE RECORD AT A TIME!

So, let us not get confused here. The PRIMARY KEY is the value, NOT the
column.

A column is a column is a column. And that does not a KEY make!

It is the INSTANCE of the Primary Key Column at the ROW that is the KEY!

A COLUMN never uniquely identifies the ROW!

An INSTANCE of the COLUMN VALUE at the ROW uniquely identifies the ROW.

Also, a relationship is not a Foreign Key, so why concat "_FK" to the end of
a Relationship name?

That is totally ARCANE!

A relationship merely links one column to another column based on common
data elements in the fields.

Get it Straight Microsoft.

John Smith

 

[--CELKO--]

A better name for CLIENT_ID is: intClientPK and intClientFK <<

A complete violaiton of ISO-11179 and basic data modeling. You name a
thing for what is nt he Universe of Discourse; not for where it is
used, not for how it is used, and not for the physical storage.
Multiple names confuse the reader, mess up the data dictionary and
invite a loss of data integrity.

 

[--CELKO--]

And this "ID" suffix thing? Who the HELL invented that for a SUFFIX?
<<

This is perfectly fine in ISO-11179; it means that the column holds a
unique value for each entity in the set represented by the table. A
column is not a column; they come in "flavors" given to them by the
domain from which they are drawn.

No, that would be naming an entity for how it is used rather than for
what it is in the data model. This is why "pk" and "fk" affixes are
wrong.

 

[Matt M]

A complete violaiton of ISO-11179 and basic data modeling. You name a

thing for what is nt he Universe of Discourse; not for where it is
used, not for how it is used, and not for the physical storage.

You've said the same thing in sqlserver.programming (and other groups), but
I'm having trouble finding the part of ISO-11179 that says name a thing for
what it is, not for how it is used. Part 4 of the standard says something
like this, but only in regard to formulating data definitions, not names.
Can you be more specific?

Matt M.