Re: SQL colum names conventions!

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: 30 Jul 2006 13:53:32 -0700
Message-ID: <1154292812.881172.14290_at_m79g2000cwm.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?
>
>
> P.S. Classis DB / SQL literature does not mention this as a
> prerequisite for proper DB design.
>
>
> Adam

If you are using table aliases like described in http://www.gplivna.eu/papers/naming_conventions.htm and prefixing each column name with that alias then you wouldn't have that problem ;) Of course I don't think that only to avoid such situations this is the only right solution. Speaking about parctical problems I've used this convention always I had enough power to enforce it (only for Oracle though) for projects involving about ~100 tables.

Your question why not tablename.columname, but only column name can be answered rather easy - why tablename.columname? Do you think that it always would give distinct values? But what if it is a self join? What if it is a join between two large subqueries, that themselves are joins? What if it is a computed column e.g. col1 + col2? I assume that aliasing is the right answer here for duplicate column names, e.g. what if you are using
SELECT a.id a_id, b.id b_id
FROM a, b
WHERE a.id = b.id (or equivalent using inner join in ANSI syntax)?

Gints Plivna
http://www.gplivna.eu Received on Sun Jul 30 2006 - 22:53:32 CEST

Original text of this message