SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql table and column (field) naming conventions

    Hi All,

    There are plenty of threads at discussing and proposing programming conventions, but (unless I'm entering poor search queries) I was surprised not to find much if any discussion regarding the naming conventions that mysql database designers/developers are using - particularly in regard to column (field) names.

    As with most proposals for naming conventions, it's more about adopting a convention that broadly 'works' for your organisation and sticking with it. So often there's no right or wrong, better or worse, convention. For example, everyone has their own preference in regard to

    - Capitalisation of the first letter of each word, eg. FirstName
    - Lower case throughout, eg: firstname
    - Underscores, eg: first_name

    Here's what we do:

    TABLE NAMES

    1. Always lower case
    2. Always singular

    eg.
    contact (a table of contact details for people/users)
    event (a table of events)
    order (a table of customer orders)
    ... etc...

    Why? The plural version of a table isn't always a case of adding an 's' (so we remove this amiguity by keeping it singular), but more importantly this approach 'agrees' with our convention on column names ...

    COLUMN (FIELD) NAMES

    1. EVERY column name to be preceded by the name of the table (including foreign keys)
    2. Capitalise first letter of each word
    3. Acronyms remain capitalised

    eg. For the table name contact, the column names might be:
    ContactID (primary key)
    ContactFirstName
    ContactLastName
    ContactCatID (this is a foreign key from the 'cat' (category) table)

    Why? In answer to convention 1: we do this to ensure, 100%, that every single column name on the entire database (assuming no tables from external sources have been introduced) are unique. Sure, we know that in our php coding we can specify which column name refers to which table (eg. cat.CatID, contact.CatID), but we kind of like the fact that our column naming conventions ensure that we _never_ have to worry about explicitly referring to 'duplicate' column names on the same db in this way.

    That's broadly it for us, barring a few other minor details.

    Interested in the mysql naming conventions adopted by other SitePointers...
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise

  2. #2
    SitePoint Enthusiast HogMan's Avatar
    Join Date
    Mar 2003
    Location
    Nebraska
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For tables I have been doing the following:
    1) All lowercase
    2) No underscores except for table prefix or table that links a many to many. So if we have a many to many between foo and bar, then the lookup table would be foo_bar.

    Field Names
    1) All lowercase
    2) Try to avoid underscores
    3) Singular
    4) I do use the table name in pk's when using a name such as id. Ie, fooid, barid.

    I guess on the note of making sure all field names throughout the entire db are unique, I would like to hear from others on this. I may have the following for example:

    foo
    --------
    fooid (pk)
    name
    ...

    bar
    ---
    barid (pk)
    name

    Then I just use aliases in the sql queries. I haven't run into any issues yet.

    The next item I was just searching for was index naming. Do these really matter as long as they are unique to the table itself? Ie, if I want to index the name colum, would I just name the index name? Or is it better to do index_name.

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Remember that case is not important in SQL, so your table could be called FoOBaRBaZ and you could still reference it by FOOBARbaz. Same goes with column names, so it's a matter of however you want to write them when you query.

    Prefixes on table names are redundant (e.g. tblFoo, tbl_Baz), so don't use those.

    Generally I stick to entity->parameter. So, a User has a First_Name, etc.

    Since SQL is case-insensitive, it helps to use underscores instead of captialization to distinguish the different words.

    As long as you are consistent, there is generally no 'best' or 'worst' method. Just a little more preferable or a little less preferable.

    You could name everything t1( c1, c2, c3 ), t2( c1, c2, c3 ) and that wouldn't be 'wrong' per se, but I would slap you silly until you changed it.

  4. #4
    SitePoint Enthusiast HogMan's Avatar
    Join Date
    Mar 2003
    Location
    Nebraska
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response MattR. What naming do you use for indexes?

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Generally I do tablename_col1_col2

    e.g.
    CREATE INDEX user_userid_password ON user( userid, password )

  6. #6
    SitePoint Enthusiast HogMan's Avatar
    Join Date
    Mar 2003
    Location
    Nebraska
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MattR
    Generally I do tablename_col1_col2

    e.g.
    CREATE INDEX user_userid_password ON user( userid, password )
    Ok, thanks for the info.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    conventions are conventions, each set is unique to the organization that set them up, and there are plenty of pros and cons no matter which way you choose

    that said, i prefer plural table names

    this is to drive home the point that you are not selecting from a table, but from a set of rows

    the only time a singular table name makes sense to me is in the case of oracle's DUAL, or the OOAK (one of a kind) table that i like to declare to hold application constants, where each constant is a column, and the OOAK table has only one row

    prefixing the name of the column with a text fragment that indicates the name of the table is quite sloppy, in my opinion, and uses up valuable space in the column name

    shorter is better, and if you need to, as matt suggested, you can qualify identically named columns with their table names or aliases

    rudy
    http://r937.com/


Bookmarks

Posting Permissions

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