Results 1 to 5 of 5
  1. #1

    database ID fields

    Just wondering everyone's take on this question of mine.

    In many books and tutorials, you often see something like this:

    PHP Code:
    CREATE TABLE persons (

    Is there any real advantage to using 'person_id' as opposed to 'persons_id' or even just 'id'? Is this solely for clarity purposes? I have seen applications using just 'id' for every table as well.

    For that matter, the naming could be totally arbitrary. Is the main reason for this to make joins easier to read?
    "The only difference between a poor person and a rich person is what they do in their spare time."
    "If youth is wasted on the young, then retirement is wasted on the old"

  2. #2
    Join Date
    Jun 2004
    apart from making it easy to read, it also helps with understanding what table that id belongs to...especially if you're working on a large projects with lots of tables.

    another things, if you add your own unique string to the name, it is less likely to cause clashes with the db's reserved variables which they will probably add to in the future...

    i can give you an example related to PHP.
    a friend of mine wrote a CMS a while back in PHP4 and when we tested it on PHP5, we found that one of his function names clashed with a new function that was added in PHP5. this is why i always name my functions/methods in the following format:
    function koobi_MyFunctionName()

  3. #3
    Join Date
    Dec 2004
    New York City, NY, USA
    Clarity basically. I usually just use "ID" (since if doing any JOINs you'd have to use syntax like tablename.ID anyway). Having tablename.tablename_ID is very redundant.

    Notice this is different than if the column name was used to define relationship with another table. For example, if I've two tables "authors" and "posts", I'd have at minimum fields in posts (presuming there is only one author per post):


    and in authors only:


    authors_ID is the ID of the author who made the post. My personal convention is to prefix these type of fields with the name of the table with which they relate with.

  4. #4
    Join Date
    Jul 2005
    The Netherlands
    The main reason people do this is that its easier to read. There are no real guidelines for naming tables and fields. Most companies have them, but these are company specific. Also these are mostly the way the person who designed the guidelines of the company likes it.

    I prefer to do it this way (example Person):

    Table will be called Persons. ID field will be called personid. So when a person has addresses you can create a table called Addresses or PersonAddresses! Just how YOU like it. Now when we add a Foreign key to the address table you call it personid (which is same field as in the person table). If you would have called it ID, then you would have a bit of a problem. Why? Then you would have 2 columns ID, 1 in Persons and 1 in Addresses. Then you create a foreignkey called personid related to the id field in Persons. That would be a bit rair. You can do it, but it looks strange.

    The easiest answer is do it the KIS way (KEEP IT SIMPLE!).

    You can always contact me for more information. I come from the Netherlands, sometimes English is still a bit difficult! LOL
    Cheap DotNet Hosting
    Windows hosting with ASP.NET and SQLServer support.
    24x7 Support Center! Prices are cheap, join today!

  5. #5
    Join Date
    Dec 2004

Posting Permissions

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