The book "SQL Queries for Mere Mortals" says to prepend the table name
to all of your columns.

Other books might say prepend the table name to the primary key column,
e.g., student_id
and then also use this name as the foreign key.

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

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

Finally, in a good database, one would use REFERENCES to explicitly
state foreign keys but some databases have to use naming conventions to
provide hints to tools which reverse engineer schemas into diagrams. I
wonder which convention they use.

Thanks for any input.

metaperl wrote:
I've seen all schemes above in real life and my only recommendation is
to find a naming scheme that you like and stick to it. The worst
situation is when people start mixing different naming schemes.

Regards,

Michael.
--
Which is more dangerous? TV guided missiles or TV guided families?
I am less likely to answer usenet postings by anonymous authors.
Visit my home page at http://michael.zedeler.dk/

Michael Zedeler wrote:
I can only agree that you have to choose an approach and then use it
consistently at least in one project. My approach can be found here
http://www.gplivna.eu/papers/naming_conventions.htm

Warning - I'v never said that it is the best one and I'v discussed with
people thinking this scheme is horrible. Usually this is just a matter
of taste and habit.

Gints Plivna
http://www.gplivna.eu/

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.

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.

Gints Plivna wrote:
The one rule of yours I disagree with is this one:
<quote>Table names are in plural form, for example, persons, materials,
addresses. </quote>

To my mind, a table of people is a table in which each row is a person.
The table should be named for the rows it has in it. Just like in C
programming an array is a container in which each element is a single
thing.

Yes, thanks for your input.

metaperl wrote:
CASE*Method: Entity Relationship Modeling. Wokingham, England:
Addison-Wesley and constantly used in such valuable books like David
C.Hay, 1996, Data Model Patterns, Dorset House Publishing and Len
Silverston, W.H. Inmon, Kent Graziano The Data Model Resource Book,
1997, Wiley Computer Publishing.
CASE*Method was adopted by Oracle and further evolved in CDM and CDMA.
This method separated logical data models i.e. entity relationship
models and physical data models that shows real tables, columns,
foreign keys and other db objects. Singular was used for logical
entities and plural was used for physical tables. Ideally data analysis
process starts with ER diagramm (or UML class diagramm I personally
don't care) and only then physical schema is generated or created
manually considering necessary implementation requirements.

So that's the reason for plural in table names.

Gints Plivna
http://www.gplivna.eu/

Not quite right; my rules are:

1) Look for an industry standard name That is, use VIN and not
"automobile_id" for the purpose identifying a car.

2) Look for a common name That is, use "ClassRoster" instead of
"StudnetTeacherCourse" for a class roster. Relationship tables are bad
about this one

3) Use a collective name. That is, we have "Personnel" and not
"Employees".

4) Use a plural name, if no collective name exists. Thus "Kangaroos"
is okay ... well if you did not know that a collective noun for
kangaroos is a "Mob", but that word is so weird that people would miss
it.

5) Use singualr name, if the table actually models a single unique
entity (or ios empty). An example might be a configuration set-up.

No quite; that is the whole point of Cantor's set theory versus
Hilbert's Hotel. We do NOT think of a set as elements; we think of the
set as a completed whole. The forest is not indivdual trees.

So, instead of "Personnel:", we should call it "DilbertWallyAlice"?
That is what naming a table for the rows it has in it implies. This
also why newbies give relationship table those weird hypenated names.
Oh, when we add "Loud Larry", the name should change?

What we are modeling at that level of abstraction is a set (personnel)
and not the elements (current employees). This also has the advanatage
in SQL of preventing a table and column from having the same name (no
problem for the parser, but bad for maintaining code).

Wrong mental model. Think of an array as a container in the abstract,
but ignore the contents, the dimensions, etc. You probably follow my
abstraction rule with arrays!

Would you name an array for the contents? No. Let me use a classic
"Eight Queens" array problem. Would you name the array "Chessboard"
whch is what it models or name it "Q1Q2Q3Q4Q5Q6Q7Q8" after its
contents? Would you name a Sodoku array "SodokuGrid" or some name
based on its 81 cells of content?

We used a system like this on the mainframe for copybooks. The problem with
our COBOL is you have to use Global Variables and none can ever be
duplicated.

So we named files like PE-11 FOR personnel and then stated each field like
PE-fieldname. In this way if we have 500 copybooks no variables will be
duplicated.

Our database system has prefixes on the newer fields to try to do the same
thing.
"metaperl" <metaperl (AT) gmail (DOT) com> wrote

"Last Boy Scout" <dummy (AT) whitehouse (DOT) gov> wrote:

allows you to use the "right" name for every field no matter how often
you use it. There is even a single statement to transfer every field
in one structure to the field with the same name in another structure.

I'm sorry, I know it is off topic but I miss COBOL's data definitions
in every language I have used subsequently so I could not help
sounding off.