Annoyance: ID Fields in SQL dabase tables

Posted December 24, 2004

You know what annoys me slighty? Let me tell you.

Assume you have a database table; let's call it tblFriends. Next assume this table has two columns, ID (which is of type autoincrement integer) and Name which is of type string/varchar/text. It stores a list of your friends, and each of them have a unique ID, which would be the primary key.

It would annoy me if you named the fields FriendID and FriendName. I assume that tblFriends.ID is the ID; it doesn't need to be tblFriends.FriendID. It's more common to see the ID field named stupidly, but both are equally unnecessary.

This annoys me slightly.

5 Comments so far

  1. Mark W on December 24th, 2004

    Don’t people use FriendID so that each column across all tables is named uniquely…that way you don’t need to prefix your column names in joins. I bet there are heaps of debates about this out there…

  2. Aaron on December 24th, 2004

    Also need to take into account reserved / special words in different DBMS …

    However like most things this really comes down to personal preference or dept/comp policy. Personally I think you should always table prefix joining fields for readability anyway … and I semi agree with mate that friend.friendname is stupid…

    Personally though this strikes me as a bit of pent up ‘tell it like it is’ :)

  3. mlambie on December 26th, 2004

    Let’s not forget we can:

    SELECT f.ID as FriendID, f.Name, a.ID as AddressID
    FROM tblFriends f, tblAddress a
    WHERE …

    All this means is that we make the prefix names as we need them: when dealing only with the tblFriends you don’t need f.FriendID because it’s obvious that an ID is a FriendID.

  4. Mark W on December 26th, 2004

    Ahhhh, I remember why you should use it now…If you have FriendID, then you can use the same column name in tblFriend and tblEnemyOf where you use it as a foreign key. Otherwise you are relying on a naming scheme of tblFriend.ID and tblEnemyOf.FriendID

    Not very compelling but it reminded me of why I use it.

    Matt, your example above is kind of arguing the counter - auto generated SQL will easily work with default column names.

  5. Aaron on December 27th, 2004

    Man I hate it when the peg colours don’t match the colour the clothes I’m putting up…

    Obscure tell it like it is reference… one of the better ones