Re: SQL colum names conventions!

From: <>
Date: 30 Jul 2006 11:29:38 -0700
Message-ID: <>

Marshall wrote:
> 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

Thanks Marshal,

The particular problem I had when I discovered this was that when I use a join on two tables and store the result in an ADO recordset, the recordset has two fields with identical names, but does NOT cry ambiguity!!! The strange thing is that when you reference that field by name say: recordset("columnname") you get always the value of the right table. Even strabger is thet the reference to recordet("tablename.columname" IS NOT ALLOWED????

Adam Received on Sun Jul 30 2006 - 20:29:38 CEST

Original text of this message