Posted by Erland Sommarskog on 07/30/06 21:11

(mail@jazzis.com) writes:
> 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?

A very bad idea. In my strong opinion, columns that denote the same
entity should have the same name all tables, with one exception: there
are two such columns in the same table.

To take an example from the database I work with, there is a table
called currencies. The primary key is curcode, and there is an uncountable
number of table where currency codes appear. And these columns are
normally called curcode. But in the instruments tables there are three
curcode columns. One is simply called curcode, which is the currency the
instrument is traded in. Then there is dividendcurcode, which is the
currency dividens are paid in. Finally there is issuercurcode, which
I don't really know what it's good for.

But there is a grain of truth in the idea. In the currencies table
there is a column for the full name of the currency, and this column
is called "curname", not just "name". And the same goes for other
tables, they have a name column, but its never called just "name", but
the column name is unique to other name columns. Because, these name
column all describes different entities.

If you employ these rules there is good chance that you run into
these name clashes on client level.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]