Re: naming conventions for columns, primary and otherwise

Yet other books would say simply call the primary key id and call the foreign key student_id. <<

That is a violation of common sense and ISO-11179 rules. A data
element name should be constant and exact. Thus, "student_id" makes
sense because you can tell that it is a unique identifier for a
student, no matter where it appears -- classes, sport teams, etc.
However, the dangling suffix "id" begs the question, "id of what??"

You name a thing for what it *is* logically within the data model. Not
for *where* an occurence is (i.e. no table or view name affixes). Not
for *how* it is physically stored (i.e. no data type affixes). Not for
its *usage* in one place (i.e. no pk-, fk- or affixes).

When you name a table (of any kind -- views, CTE, derived, etc.), first
look for an industry standard name, then for a common familar
collective noun and finally for a plural name. Never use a singular
name for a table because it is a set; the exception is when it actually
has one and only one element.

Does anyone have any feedback on the "best" way to do this? Which books would you recommend for reference on this topic? <<

Try my SQL PREOGRAMMING STYLE. I give a long set of conventions based
on the ISO-11179 rules and industry practices. I looked at the Data
Warehouse people since they deal with data at the broadest level of the
enterprise. These are the guys that design data dictionaries.

.

Relevant Pages