Re: database constraints

On Thu, 7 Oct 2004, David Garamond wrote:

> With all due respect, David, everybody is entitled to his own opinion and
> yours is not the absolute truth. Column and table naming is not exact
> science. Naming every single-column PK as "id" has advantages over
> "<tablename>_id":
>
> - you instantly know that "id" is PK;

You mean if you're looking at table "X" it takes time to you to
identify "X_id" as the PK? I don't get why just "id" is better in
this respect.

> - renaming tables does not need to a PITA (to be consistent with the above
> "<table>_name" scheme you would have to rename all the column names too).

(1), see below.

>
> - it's shorter;

Agreed. But is shorter "better"? How about writing a program using
"a", "b", .. "aa", "ab", .. "xyz" as variable names? Isn't it shorter?

> - etc.

I think I can list this one among the advantages of "<tablename>_id"
as well. :-)


> And besides, what is exactly the advantage of having unique/prefixed column
> names across all tables (aside from being able to use JOIN USING and NATURAL
> JOIN syntax)? Every column name can be fully qualified with their table names
> (and their schema name) anyway. And it's unlikely that someone who is
> familiar with computing could misunderstand "id".
>
> Personally, I like column names to be short and to the point. Instead of:
> CREATE TABLE person(person_id ..., person_name ..., person_dob ...) I prefer
> CREATE TABLE person(id ..., name ..., dob ...). It matches the way I name my
> Perl/Python/Ruby/etc. classes' attributes.
>
> Of course, everyone can choose his or her own style.

The advantage is that you can use the same name for both the PK column
and any FK columns that reference to it. It's useful for JOINs but not
only. If you're using any FK, your (1) doesn't hold for "id" as well:
you'll have to rename FKs anyway.

Since you have to somehow qualify FKs, there's no reason not to use the
same name for PKs in their own table.

Using the same name for the same object (and different names for
different objects) comes NATURAL. :-)

NATURAL JOINs are only part of the problem. When joining two tables,
you may want to know which column comes from which table, in the resulting
one. Sure, you can rename them as in:

SELECT a.comment as a_comment, b.comment as b_comment FROM a JOIN b;

so that in the resulting table you can tell which is which.
But, doesn't the need to _rename_ things hint about a naming problem? :-)

Of course, I'm not stating the "<tablename>_id" is your best, your last
and your only naming convention. It's just one. A decent one. Having
_no_ naming convention is a little disaster.

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it