Re: SQL colum names conventions!

From: Marshall <marshall.spight_at_gmail.com>
Date: 30 Jul 2006 10:56:52 -0700
Message-ID: <1154282212.382499.273280_at_m73g2000cwd.googlegroups.com>


mail_at_jazzis.com wrote:
> As a result of ambiguity problems with colum names when performing a
> join I discovered that MS SQLServer does not return the conventional
> tablename.columname but only columname, which of course creates
> problems.
>
> I have read quite a lot on the subject and some people claim that a
> colum name should be UNIQUE in a database, i.e. rather than having
>
>
> table1.column
> table2.column
>
>
> the design should be
>
>
> table1.colum1
> table2.colum2
>
>
> This would solve ANY ambiguity as to colum names especially in joins.
>
>
> What is your opinion on that?

Seems like a bad idea to me, if not actually impractical. Considering all the columns of all the tables to be in a single namespace gets crowded. When you add that 500th table, do you really want to check all 499 other tables to make sure you're not colliding on an attribute name? That will lead to some weird names, too.

Every table defines its own namespace for attribute names, and this is for a reason. Now, SQL is a bit odd in that we have this "join" thing that pushes two namespaces togeter. But it also has excellent renaming facilities, so you can make the result be whatever it needs to be.

It's not uncommon for two attributes to have the same meaning. For example, a foreign key and the primary key it references are of the same domain. Giving them the same name is entirely appropriate.

At work, the conventions for the mondo big schema that I've worked on call for primary key id columns to include the table name, so you have globally-unique key names, but then these key names can be reused as foreign keys where appropriate. It seems to work well.

Marshall Received on Sun Jul 30 2006 - 19:56:52 CEST

Original text of this message