2 tables with identical column names
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: 2 tables with identical column names

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    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 ?


    Thanks
    Sonali
    Sonali

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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.

    Anyway, there's my .02

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by sonaliak
    Some one here has designed 2 tables... Table name AAA and BBB, but the column names in these 2 tables are exactly identical...
    Since chrisrlong didn't pick up this opportunity, I'll take it....

    Unless there is VERY good reasons to have two tables with the exact same structure, you're probably looking at a poor design.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Hehe - good point Jeff.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===========
    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.

    All data element in a RDBMS must follow Prime_Qualifier_Class structure.
    The prime word describes the subject - what the subject is. Some examples are account, customer, city etc.
    Class word further classifies the prime word i.e date, id , total, etc.
    Qualifier describes (or qualifies) the prime word further. Some examples are start, end, primary and secondary.

    Some examples for PQC are ACCOUNT_START_DATE, ACCOUNT_END_DATE.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Chris, I can't wait to see your reply on this....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by tamilselvan
    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.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    I can see it now....

    Code:
    CREATE TABLE BankInformation (
      BankInformation_IDNumber_Num       NUMBER,
      BankInformation_Name_Char          VARCHAR2(20),
      BankInformation_AccountNumber_Char VARCHAR2(50),
    ...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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).

    Immagine we had to write simmilar guts for any dictionary query:
    Code:
    SELECT
      TABLE_owner,
      table_name,
      TABLE_tablespace_name,
      TABLE_pct_free,
      TABLE_pct_used,
      TABLE_initial_extent,
      TABLE_next_extent,
      TABLE_pct_increase,
      TABLE_num_rows,
      TABLE_blocks,
      TABLE_empty_blocks
    FROM dba_tables;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width