#1
  1. No Profile Picture
    http://stealthwd.ca
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2005
    Posts
    691
    Rep Power
    200

    Primary key name


    Hey everyone. Whenever I create a database table, I more or less always have a primary key that autoincrements. Usually I name the primary key according to the table name, so something like userID, customerID, orderID, etc etc etc, you get the idea. Lately I've been getting into ways of reducing code. To make a long story short I was wondering what people thought about having all the primary keys have the same name, so for example just priID, modID, or something like that. Obviously the downside to this is in join queries I would have to specify the table before the key, which i do anyways. Any reasons why I shouldn't do this? The way I have the rest of my app setup this could save me lots of coding I think.

    THanks!
  2. #2
  3. Moderator Emeritus
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    7,188
    Rep Power
    2265
    I use "id" for the pkey in a table; any table that needs to reference another table (fkey) uses the table name _id. For instance, if I have a table named "Contacts", the pkey will be "id"; if Contacts references the Address table, it will use an fkey named "address_id", which points to the pkey "id" in the table "Address".

    Simple naming convention; seems to save a bit of time while coding.

    Comments on this post

    • Winters agrees : Random rep for previous spite ;)
    DrGroove, Devshed Moderator | New to Devshed? Read the User Guide | Connect with me on LinkedIn
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Location
    UK
    Posts
    197
    Rep Power
    150
    Well ... this is the stuff that wars are made of! so this is just the way I happen to do it, help yourself to any bits you like, ignore anything that stinks ...

    If I have a module "Sales Order Processing" and tables for "Order Header" and "Order Items" I come up with mnemonics. Lets say "SOP" and "ORDH" / "ORDI". The tables and columns use these:

    SOP_ORDH_OrderHeader
    SOP_ORDI_OrderItem

    The table name is obvious to someone not familiar with the mnemonics from the last part. All columns in the table use the table names mnemonic as a prefix (but in lower case):

    sop_ordh_ID
    sop_ordh_Total
    sop_ordh_Date
    sop_ordh_Customer

    sop_ordi_sop_ordh_ID -- Joins to Order Header ID
    sop_ordi_Product
    sop_ordi_Qty
    sop_ordi_PriceEach

    Actually sop_ordh_Customer would be a link to the Registration table in the Members module, so would be sop_ordh_mem_reg_ID

    And sop_ordi_Product would be a link to the Product table in the Stock module, so would be sop_ordi_stk_prod_Code

    I use "ID" for auto-incrementing numbers, "CODE" for user-allocated alpha-numeric codes (e.g. a product code - although the Product table might also have an auto-incrementing ID column) and "REF" for things outside our control - e.g. 3rd party references which might contain spaces, backslashes, goodness knows what!

    Every column name in the DB is unique. Just a column name such as [sop_ordi_stk_prod_Code] uniquely identifies the Table as well as the column. Naming them xxx_yyy_Zzzzz means that they are future-proofed from collisions with reserved words.

    All working variables (both in SQL, Stored procedure parameters, etc. and in the Application) use the same name - sometimes with suffixes where necessary - such that if we need to change size / type of the column a global FIND will hit all usage (and sometimes we can do a global REPLACE to make the change )

    In JOINs this reduces accidental bugs, as drgroove said. For example:
    Code:
    FROM SOP_ORDH_OrderHeader
        JOIN SOP_ORDI_OrderItem
            ON sop_ordi_sop_ordh_ID = sop_ordh_ID
    provides visual feedback that the right columns have been used, whereas:
    Code:
    FROM SOP_ORDH_OrderHeader
        JOIN SOP_ORDI_OrderItem
            ON sop_ordi_sop_ordh_ID = sop_ordh_ID
        JOIN MEM_REG_Registration
            ON mem_reg_ID = sop_ordh_ID -- This is clearly wrong!!
    makes it more visible that the JOIN is wrong (and given that all IDs are incrementing numbers it might work fine in testing!).

    The only time we need dis-ambiguificiation (is that a word?!) of column names is when there are self-referencing JOINs

    I suppose I see this as just an extension of "Have an ID column in every table, if you have a column that joins to another table call it "TableName_ID" - I'm basically just extending that to have "TableName_" (albeit abbreviated) in front of every column, thus using "[Date]" in a SELECT list would be ambiguous, you might prefix it with "[OrderHeader].[Date]" so that it is more obvious, or indeed unambiguous if there are multiple columns called [Date] in the tables being selected from, so I reason that just calling the column [sop_ordh_Date] is much of a muchness
  6. #4
  7. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Location
    Switzerland
    Posts
    1,152
    Rep Power
    1902
    Originally Posted by drgroove
    I use "id" for the pkey in a table; any table that needs to reference another table (fkey) uses the table name _id. For instance, if I have a table named "Contacts", the pkey will be "id"; if Contacts references the Address table, it will use an fkey named "address_id", which points to the pkey "id" in the table "Address".

    Simple naming convention; seems to save a bit of time while coding.
    I pretty much do the same thing except I use "dbid" as the name of my PKeys since ID is a keyword and that way I don't need to add those strange quotes...
    - Hugh of Borg

    The first thing young borg are taught: Keep away from Microsoft software!
  8. #5
  9. Moderator Emeritus
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    7,188
    Rep Power
    2265
    Originally Posted by Hugh of Borg
    I pretty much do the same thing except I use "dbid" as the name of my PKeys since ID is a keyword and that way I don't need to add those strange quotes...
    Errrrr... not seeing "id" as a keyword for mysql ... unless I'm reading this wrong, or have missed something in the documentation.
    DrGroove, Devshed Moderator | New to Devshed? Read the User Guide | Connect with me on LinkedIn
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    May 2004
    Posts
    3,417
    Rep Power
    887
    Originally Posted by drgroove
    Errrrr... not seeing "id" as a keyword for mysql ... unless I'm reading this wrong, or have missed something in the documentation.
    News flash: mysql is not the only database engine in the world. Database vendors are not required to conform to the mysql docs. I am pretty sure there's nothing in the SQL standards that prevent some of those vendors from designating their own keywords.
    I no longer wish to be associated with this site.
  12. #7
  13. Meatball Surgeon
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 2004
    Location
    Elbow deep in code
    Posts
    2,056
    Rep Power
    1321
    I used to support an out of the box softare package that did just that (anyone ever heard of Cristal?). Every key, in every table was named OBJID, for hundreds of tables!

    Actually it was nowhere as bad as it sounds because they did have one naming convention that I love so much that i still use it today. I make sure that all reference columns have a naming convention of "Table2table"

    So as an exmple, if I had a User table and i had to link the adress table to it, on the Address table i would add a column called Address2User. That way i always know what that column is used for (and new developers can quickly move thru my database very easily)

    Comments on this post

    • jwdonahue agrees
    Three gigs for the secretaries fair
    Seven gigs for the system source
    Nine gigs for the coders in smoky lairs
    One disk to rule them all, one disk to bind them
    One disk to hold the files, and in the darkness grind'em
    ---------------------------------------------------
    It is by caffeine alone that I set my mind in motion.
    It is by the beans of Java, that my thoughts acquire speed.
    The hand acquire shakes; the shakes become a warning.
    It is by caffeine alone that I set my mind in motion.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Location
    UK
    Posts
    197
    Rep Power
    150
    When I read the "Newsflash" bit it stepped on my "Idiot nerve" (I like that phrase!), re-reading it now I can easily read it in a different light. I went and had a look in the SQL-92 specification, no indication that it was considered as a reserve word back then (have they issued a more recent specification??). But ID, or even FooBar for that matter, could become a reserve word in the future.

    I'm new here, and as such I hesitate to interrupt, but the point I would like to make is that the reason I am here is because in other threads I have read, and those I where I have tried to help, and in the replies I have received, the whole "tone" of this site is much more courteous than most that I visit where "I need to solve this urgently, reply immediately" pervades, presumably from $0.01-a-day ill-trained people expecting the forum community to do their work for them.

    I'll suggest back to topic please chaps, and "jwdonahue" you sound like a thoroughly decent chap, maybe edit your original post and the moderators can then delete the ensuing wibble and make a nice clean thread for the next person that comes looking for an answer to the PK Naming convention question.

    And a Happy Christmas to all
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Location
    UK
    Posts
    197
    Rep Power
    150
    "I had a User table and i had to link the adress table to it, on the Address table i would add a column called Address2User"

    Not come across that before. Was there a naming-convention where the JOIN had multiple columns? or perhaps everything was AUTO-IDENTITY and thus it didn;t arise - and putting aside whether multiple-column JOINs are be a good idea, or not
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    May 2004
    Posts
    3,417
    Rep Power
    887
    Originally Posted by Kristen
    I went and had a look in the SQL-92 specification, no indication that it was considered as a reserve word back then...
    Actually, I think I have encountered cases where it is not exactly a reserved word in the absolute sense, but that when you enable the auto-id feature on a column in a table, it then comes into play on some database engines. I don't recall the details really, it's been too long and databases are not my main line of work. Perhaps it was one of the Sybase products? At any rate, "ID" is not the most portable field name and Hugh of Borg expressed that fact, however imprecisely.

    [off-topic]And yes, I agree, the tone of this site is better than many sites out there. I have always tried to keep it that way. NOTE that we are dealing with the issue on back-channels at the moment. My apologies for the interlude.[/off-topic]
    I no longer wish to be associated with this site.

IMN logo majestic logo threadwatch logo seochat tools logo