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

Thread: 2 tables with identical column names

  1. #11
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tamilselvan
    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"?
    Never. At least not if you have some commonly accepted SQL coding standards in effect. No matter how unambigously your columns are named in your tables, you should still put table name (or alias) in front of your columns in SQL every time you use more than one table in your SQL.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Again poor understanding.

  3. #13
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by tamilselvan
    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.
    So now we have column name prefixes that are actually different than the table name they are in? I would vote for consistency in this case. Either both the table name and prefix should be abbreviated or neither.

    However, this doesn't solve the problem. At my current client, they have a very extensive list of abbreviations. Nonetheless, there is a table in the database named 'RGLTR_INDVL_DSCLR_NTC_QUEUE_ST'. Doesn't leave much room for a column name, does it?

    Of course, we could then resort to prefixing columns with the table alias. The rule in my standard for alias names is the first letter of each word in the table, so we would have RIDNQS_ prefixing all the column names in the table.

    We just lost 7 out of 30 chars for each column name. That's over 20%!

    That would be difficult for other column names we have like 'IA_RNWL_PRCSG_FEE_TTL_COST_AM'. Not much room for a table prefix, is there?

    Of course, this also leads to silly things like Emp.E_ID.

    Originally posted by tamilselvan
    Take your design.
    CITY table
    id varchar2(2)
    name varchar2(30)
    Actually, if you look at my original post, you'll see that I strongly advocate naming the PK after the table. Therefore, the PK would be CITY_ID.

    But lets take a look at *your* design. ID VARCHAR2(2)? I thought we had discussed how much more efficient numeric PKs were already. I know I've preached it until I'm blue in the face around here, but I guess you still don't believe me. Again, your prerogative, but I do have the numbers to prove it.

    You also have 2 'name' fields that are of different lengths. In my standard, all 'name' fields are 30 characters and all desc fields are 60. When you can easily see that the names are the same, you can more easily see when the types are not. Consistency in conventions allows for a more consistent database and fewer surprises.

    Originally posted by tamilselvan
    The new people who look into your code defintely will not be happy once the original developers leave.
    Now, while my clients are generally sad to see me go, it is certainly not because I'm leaving them with a bad database or code. There are many aspects to maintainability. I will put my code readability up against anyone. I am the most anal person you may ever want to meet when it comes to code readability, documentation and cleanliness. And even if I wasn't, I've already addressed the fact that I find my standard more readable in and of itself.

    Originally posted by tamilselvan
    You guys poorly understood.
    Actually, I think we understood just fine.

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

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