Re: Database naming convention (yet another post of it, but a bit different)

There's really no such thing as "wrong" in these conventions. But I've offered some alternatives,
and my reasons for going with those alternatives. My reasons might not be valid for anyone else.
In order for conventions to be come widespread, they have to appeal to a lot of people who can choose to adhere to them or not.

> 1) tables names are in singular, not to get confused with adding or

The convention that I adopted was that table names are plural (or collective), but
table aliases are singular, and abbreviated.

Example:

from EMPLOYEES E

... E.LAST_NAME The plural makes more sense to me in the "from" clause.

> 2) tables and field names are all lower-case, I just prefer it for
> simplicity, and some engines or utilities may convert cases of table
> and field names.

I used to use a DDL extraction tool ($ RMU/EXTRACT if anybody cares) that put all the datanames in upper
case, and all the SQL keywords in lower case. I got used to that, and I like it. All keywords
in C, C++, and Java are lower case, so it makes it easier to read SQL if all the keywords are lower
case. By making the data names upper case, it makes them stand out.

> 3) When using a foreign key, I use as the field name, the same name of
> the table that the foreign key is pointing to. Example:

When using a foreign key, I try to use the same column name as the column name where it
is the primary key. (The candidate key chosen to resolve the reference)
> 4) Use underscore to replace spaces. like_this

Agreed.
> 5) Do not abreviate! it's not at all expensive to write some letters
> more, and it's more descriptive and readable. Copy & Paste will
> allways save you the effort. example: use Mortgage_index rather than
> mrtg_idx, using id for identification or identificator is ok, though.
> ;D

Agreed, except for a few "standard" abbreviations, like the one you mentioned.

> 6) Do not try tu use prefixes refering to data types,
Generally agreed.

> 7) Do not repeat the table name in a field's name, I've seen this many

> Because when you refer to it, you can write "employee.id" and there
> won't be any name space problems.

I disagree. If two columns have the same name, they should be drawn from the same domain.
CLIENT_ID and EMPLOYEE_ID are drawn from different domains, and in my view, they should have
different names.

Finally, all these things are a matter of preference, not axiomatic. My last comment is: "De gustibus non disputandum est"." Received on Wed Oct 09 2002 - 13:01:39 CEST