Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: Column Name Prefix or Table Alias

    Ok,

    So im currently at a cross roads, and not sure which way to go.

    At the moment, I currently underscore my column names with the table name...

    this is handy in my joins, as i never need an alias, as its always unique

    ie:
    table 1: employer_id, employer_given_name, employer_surname
    table 2: department_id, department_employer_id, department_name

    SELECT * FROM employer JOIN department ON department_employer_id = employer_id.

    but, gets a little tedius as the table numbers grow.

    so, is it easier better (by experience, i guess from people developing LARGE scale databases to use aliases instead of column prefixes...)

    i dont like the 1-2 letter prefixes that you get, as in Employer E, Department D, and would use a min of 4 letters for my prefixes...

    just after some general advice.

    Cheers

  2. #2
    Join Date
    Feb 2007
    Posts
    62
    Personally I hate it. It's up there with Hungarian notation. It adds nothing. You always have to prefix your column names in queries anyway if there is ambiguity and the parser will tell you this. If it bothers you, then qualify every column with the table name even if it's unambiguous.
    The only exception is foriegn keys, where it makes sense to have a employer_id in your department table, to use your rather bizarre example.

    I think you've somewhat missed the point about using aliases by the way. Call them what you want or don't use them at all (unless doing multiple joins to the same table).

  3. #3
    Join Date
    Aug 2005
    Posts
    55

    Unhappy

    Quote Originally Posted by LoztInSpace
    Personally I hate it. It's up there with Hungarian notation. It adds nothing. You always have to prefix your column names in queries anyway if there is ambiguity and the parser will tell you this. If it bothers you, then qualify every column with the table name even if it's unambiguous.
    The only exception is foriegn keys, where it makes sense to have a employer_id in your department table, to use your rather bizarre example.

    I think you've somewhat missed the point about using aliases by the way. Call them what you want or don't use them at all (unless doing multiple joins to the same table).
    well, i think it was my bizarre example that i missed the point of alias's.

    but the method should be the same. when joining 2 tables, if the column names are ambiguous, i get an error. i can either use an alias, to prefix my columns to prevent an alias been needed. but in a database where there are heaps of tables, and then each column needs a table prefix...seems a bit much. but i am very big on readability, and it adds to that.

    but yeah. i think i just answered my own question, and might start with alias's now. but still just after some general input.

    Cheers

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my rule of thumb: always use a table prefix on every single column mentioned in a query, whenever the query includes more than one table

    the prefix can be either the table name itself, or a table alias
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    My rule of thumb: be consistent.

    I mean, you're going to have to start using aliases if you use self-joins aren't you?


    ..personally I dislike that naming convention you are using!
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I worked with a third party database that had a unique four letter prefix for each table and that prefix was included as a prefix for every column name. So table PEOPpeople would have columns PEOP_person_id, PEOP_forename, PEOP_surname. (for example) the people-departments table structure would be something like PDEPpeopledepartments PDEP_person_id.

    I hated it when I first saw it but it grew on me the more I used the database, especially using intellisense (SQL Prompt). I've never used it in any of my databases - the advantages I found weren't enough to motivate me to bother accounting for this in the implementation.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I work on a similar legacy database with the most irritating naming conventions

    dbo.tblvacancy_department_details.tbl_vacancy_depa rtment_details_id
    dbo.tblvacancy_department_details.tbl_vacancy_depa rtment_details_date_created

    It reminds me of that addage in programming: if you're copying and pasting, something is wrong.
    George
    Home | Blog

Posting Permissions

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