SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    518
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Naming conventions - prefixing table and field names?

    When I started my current (and first) developer job I inherited a database table naming convention which used prefix of 't' on every table eg tOrders, tProducts etc. Is there any point in this? The only use I can see for this is to differentiate tables from views, but is there even any point in this?

    I was reminded of this today, when, as part of an interview process, a candidate demonstrated some work which used 'tbl_' at the start of every table name. Not only this, but he prefixed each field name with 'fld_'. I really can't see the point in this, but I'm presuming he picked the habit up somewhere.

    On the topic of prefixes, I do find myself using field names like productID, productName etc. Is it more fitting/clear/easy to just use 'id', 'name' as field names, as id and name are attributes of (for example) a product? So a product's name or id can easily be referenced by product.name, product.id, or using a suitable alias. Or would this make queries with multiple joins ultimately more difficult to read?

  2. #2
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Everyone will have their own preferred convention so I can only speak for myself. I think using prefixes like 't' for tables or 'fld_' for fields doesn't add anything of value apart from requiring more typing. I rarely use table prefixes and if I do they denote different components of a system, for example 'shop_', 'inv_' (inventory), 'forum_', 'log_', etc.

    I usually don't use 'productName', simply 'name'. But for primary keys I use the table prefix like 'product_id' - just because most often I will use the same name for corresponding foreign keys in other tables and this way I keep the naming consistent. I think this doesn't matter much in sql itself but in an online application I may be using 'product_id' in many different contexts (variable names, array keys, object properties, template variables, xml elements or attributes, form field names, url query strings, etc.) so I find it easier when I have the same index names accross all my code - insted of an 'id' everywhere.

    Other conventions I stick to are small caps for all table and fields names, and upper case for functions and stored procedures (just because native mysql functions are upper case).

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    completely agree with all of lemon juice's suggestions, with one slight difference

    the primary key, if it's an auto_increment, is called "id"

    foreign keys which reference it are called "entity_id"

    this is so that when you run across a query which joins multiple tables, with ON conditions such as inventory.product_id = products.id, it is immediately obvious which one is the foreign key

    this structural characteristic is not evident if the join condition is inventory.product_id = products.product_id -- you can guess which one is the parent table in the relationship and which one is the child, but you'd have to examine the table designs to be sure
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    btw, regarding the "fld_" prefix, this has been discussed here before --

    http://www.sitepoint.com/forums/show...=1#post4553440

    i've never actually found anyone in real life using the "fld_" prefix who was able to offer a valid reason for doing so

    nor, come to think of it, for using the "tbl_" prefix either
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    inventory.product_id = products.id, it is immediately obvious which one is the foreign key
    [...]
    inventory.product_id = products.product_id -- you can guess which one is the parent table in the relationship and which one is the child, but you'd have to examine the table designs to be sure
    Actually, I don't see a difference. If I stick to the same principle everywhere then when I see products.product_id I am sure that this is primary key, just as cats.cat_id, users.user_id, etc. There is no other possibility.

    I'd agree that for writing sql using plain 'id' for PK is very slightly more convenient but I use 'product_id', etc. for the other reasons I listed in my post. In an online application plain sql is used rarely compared to other pieces of code where the same names of columns appear as variables and other kinds of entities.

    The OP forgot about one more convention: plural vs singular table names - but maybe don't let's start an unnecessary discussion

  6. #6
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    518
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    but maybe don't let's start an unnecessary discussion
    Let's.

    I limited my OP to those few examples because I thought the general issue may have already been debated to death on the forum. If not then bring it on!

    I must confess that, when it comes to id fields and foreign keys, I do find it handy to call them the same thing, so that I can make my join with USING(idfield). Does that make me a bad person?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    There is no other possibility.
    actually, there is, but let's not prolong an unnecessary discussion

    i think we're all agreed that the "tbl_" and "fld_" prefixes are useless

    as for differentiating between tables and views, i don't see the reason why you'd want to do that -- in fact, it would be counter-productive, because the whole point of views is to define a particular data definition to look like a table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    Let's.
    it's really more of a personal preference

    mine is for plural table names

    that way, your e-commerce database doesn't have to wrestle with customer, product, and `order` (or "order" or [order], depending on your sql dialect)

    also, it is obvious that a table is a collection of row, and most queries are interested in retrieving multiple row


    Quote Originally Posted by hessodreamy View Post
    ... make my join with USING(idfield). Does that make me a bad person?
    nope, you've just never run into the problem with using USING in multiple table joins

    avoid USING and you'll never hit this problem
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    Let's.
    Most often I use singular just because I translate most tables to PHP objects, so that $product object makes sense as a singular because it contains data of one record. For sql itself plural makes more sense as a table is a collection of multiple items. But either way is fine for me, no big deal.

    Quote Originally Posted by r937 View Post
    actually, there is, but let's not prolong an unnecessary discussion
    I think I know what you mean so I will just say briefly that if I stick to the naming convention consistently there isn't

    Quote Originally Posted by r937 View Post
    as for differentiating between tables and views, i don't see the reason why you'd want to do that -- in fact, it would be counter-productive, because the whole point of views is to define a particular data definition to look like a table
    Actually, recently when I started using views I had a reverse problem - how to name the table which is NOT a view? I decided to have a product table with all raw data but actually use a view of the table almost everywhere. I assumed in such case it would be logical to name the view 'product'. Then what do I do with the actual product table? I decided to call it 'product_data'. What's your take on this?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    What's your take on this?
    depends entirely on the purpose of the view

    sorry, wimpy answer, i know

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •