Re: database constraints

David Fetter wrote:
>>>BTW, "id" is a terrible name for a column. Better call it foo_id.
>>
>>I disagree with the idea that "id" is a terrible name for a column. The
>>only negative to it, is that you will have to be explicit in your
>>declarations when doing joins and such... ex:
>>
>>SELECT * FROM foo
>> JOIN bar on (foo.id = bar.id)
>>
>>Personally I would rather see, and write that then:
>>
>>SELECT * FROM foo
>> JOIN bar on (foo_id = bar_id)
>
> With all due respect, Josh, naming your columns with decipherable
> names, i.e. *not* having 50 different things called "id" in your db
> helps enormously with maintenance, especially when the current
> maintainer has never met the designer, a common situation. Also, many
> databases have documents that are inadequate, out of date, or both, so
> decipherable names, along with as much other self-documentation, is a
> big plus.

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;

- 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).

- it's shorter;

- etc.

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.

--
dave