If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Some one here has designed 2 tables... Table name AAA and BBB, but the column names in these 2 tables are exactly identical... including the Primary key name.
These tables will store different data but will be in same schema.. I did not like the idea of having 2 tables with same column names. Are there any issues that we will face with having the same column names or is it okay to have same names ?

I use the same column names all the time. All my code tables, for example, have a name and desc field and most of my other tables have a timestamp field and so on. I see no problem with that. As a matter of fact, it makes a lot of automation of the code creation possible. (I have an entire ERwin template that automatically generates all the CRUD). The only issue is when combining the same-named column from different tables in the same resulset - one or both must then be aliased.

Now, naming 2 PKs the same is quite odd, IMHO. The PK should be named based on the table it is in. As in Emp.Emp_ID or Client.Client_PK or State.State_CD or whatever. The PK is the one field that will definitely find it's way into other tables and therefore needs to identify its origin. I can't even imagine why you wouldn't name the PK after the table.

===========
I use the same column names all the time. All my code tables, for example, have a name and desc field and most of my other tables have a timestamp field and so on. I see no problem with that. As a matter of fact, it makes a lot of automation of the code creation possible. (I have an entire ERwin template that automatically generates all the CRUD). The only issue is when combining the same-named column from different tables in the same resulset - one or both must then be aliased.
============
This indicates poor database object naming convention and design also.

Trust me, my good man, I will put my database design practices up against anybody, any time.

But let's try to stay focused on my naming conventions, which I happen to believe are better as well.

Originally posted by tamilselvan All data element in a RDBMS must follow Prime_Qualifier_Class structure.

Must? I think not. It is simply a standard that people have developed just like Hungarian notation was, and that standard truly sucked, IMHO.

Prefixing every column in the table with the name of the table is wasteful, IMHO. There's often an awful lot of information that must be squeezed into that column name, and saving that extra space can be important, especially when the table name itself is very verbose. You start sacrificing column-name clarity to make room for a duplicative piece of information. Doesn't seem like a worthwhile tradeoff to me.

When one practices proper SQL standards, and prefixes every column in a multi-table SELECT with an alias, one can see that having the table name as part of the column name is truly redundant.

I also feel it enhances readability even when name length is not an issue. I can look down a list of columns and see real, left-justified names, just like in the rest of life. Prefixing every single column with the table name means that the real, useful information doesn't start until several characters into every column, making it hard to read at a glance.

I've also been able to automate a lot of code generation and tasks because I have consistent names. I can automatically check my optimistic concurrency on updates with the TimeStamp column without having to know what the tablename is. Not that this would be insurmountable with another standard, but it is much cleaner and easier this way.

The only issue comes when multiple columns of the same name are in the same SELECT clause, in which case an alias is required.

Now, you may feel that this one drawback is worse than all the advantages I listed. That is your prerogative and your opinion. But don't assume that my standard is 'poor' out of hand, son. You just might get my 'ire' up one of these times.

Thank god Oracle didn't follow that naming convention when they designed the data dictionary (although I would be extremely thankful to their current designers if they would be more consistent with their naming standards).

You guys poorly understood.
First of all I did not say every column should be started with the TABLE name. For example, the table name could be ACCOUNTS, the column name can be ACCNT_END_DATE, ACCNT_START_DATE.

It is always better to use abbreviation, if the length of the subject is too long. Some examples are CUST for CUSTOMER, ACCNT for ACCOUNTS etc.

Take your design.
CITY table
id varchar2(2)
name varchar2(30)

STATE table
id varchar2(2)
name varchar2(2)

How many timed do you encounter "columns are ambiguously defined error message"?

The new people who look into your code defintely will not be happy once the original developers leave.