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

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

Typical logical modeling standards that I have seen at
various companies usually state that attribute names are
unique within the model, but not the enterprise.  This
doesn't address the typical audit columns one normally sees
in physically implemented tables however.  How are those
folks that state that every column must be unique handling
columns like last_update_ts?