Re: SQL colum names conventions!
Date: 30 Jul 2006 13:53:32 -0700
> 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
> 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
> the design should be
> 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.
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)?
http://www.gplivna.eu Received on Sun Jul 30 2006 - 22:53:32 CEST