Re: database constraints

David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> writes:

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

Sure. But let me put another big vote in favour against the "id" naming scheme
and for the "table_id" naming scheme.

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

Sure when you're just comparing simple queries like:

select * from foo,bar where foo.bar_id = bar.bar_id
select * from foo,bar where foo.bar_id = bar.id

there doesn't seem to be much difference. Though the ability to use USING instead
of ON sure does clean things up quite a bit:

select * from foo join bar USING (bar_id)

However, when you have more complex queries things are not so clear. Once you
have a couple levels of subqueries with joins in them it's no longer so clear
what "subquery1.id" is any more. For that matter there could be two or three
"id" columns in the subquery that you would like to refer to.

I've found that in the long run I saved a whole lot of time and energy by
religiously going around enforcing a "one name" policy. My database column
names for the same data type always match, the application variables match the
database column names, and the other layers (html forms) always match the
database column names and application variables. The only exceptions are when
I have to distinguish which of several relationships the other foreign key
bears.

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

I do agree about avoiding things like person_name, person_dob etc. Though
sometimes it's handy just for avoiding keywords like "desc", "user", etc.

--
greg