MORE ABOUT ME

Use What Works: Prefixing Database Tables With ‘tbl’

3 Pingbacks/Trackbacks

  • crumpley

    I use prefixes for table names for exactly the reason you mention. I also continue to use a variation of the Lezinski Naming Conventions for program variables. Why? They make my life easier, again for exactly the reason you mention.

    The person criticizing this practice seems to belong in the category that Joel Spolsky calls Architecture Astronauts. I agree with Buck Woody–use what works.

    • ThomasLaRock

      Exactly my point…use what works for you.

      • Malathi Mahadevan

        Don’t wishs to contradict the many wise opinions here but some thoughts having supported many applications over the years: 1 It is important to know a table from a view – especially if you have little time and a LOT of code review to do.Where i work we have to give a time estimate for a code review – depending on how many stored procedures/functions there are. If i look at something that *looks* like a five table join but is actually five views that drill down into fifty views and 200 tables (has happened), then my estimate is wayyyy of the mark. That is where ‘tbl..’, ‘vw…’ comes in handy. 2 I get the point on refactoring – but refactoring on a large level or even splitting a much used table into parts and so on are *very* difficult to do in a highly used production environment. When you can absolutely prove the performance gain on an important query that is very significant to them by doing something of that nature then you can perhaps get the go ahead but not becuase you think the person before you designed it poorly or it is some query that shaves off a few seconds that makes you thrilled as a DBA but management couldn’t care less for the gains. In short, most refactoring does not happen the way it should – it is bandaided unfortunately to the extent possible. When large scale refactoring happens there is usually time to name objects appropriately. 3 What would you do if inherit something someone did? Very rarely do people get to support somethign they designed from scratch from start through lifecycle…so if the person before you was a naming fanatic and you think it is totally wrong – would it be enough grounds to refactor the whole thing? I dont think so – but if there is any company that allows for that am much interested in working for them!! In short like Tom said earlier ‘do what works best’. Puritanical stances are nice for debate but unfortunately the real works is a lot tougher!!

        • ThomasLaRock

          Thanks Mal!

          • ThomasLaRock

            Synonyms? Do people actually use those?

          • Aaron Bertrand

            Are you serious? If so: yes. Absolutely yes.

          • https://plus.google.com/u/0/112139096945704957567/about sqlrockstar

            I’ve never seen synonyms used except in BOL examples or very old code. Would they help reduce the urge to use a prefix? Or just make the concept worse?

          • http://www.facebook.com/rojipt Roji P Thomas

            Yes. In the same way views are used to encapsulate vertical/horizontal partitioning, synonyms are used to support AB table switching , Snapshot usage during ETL , Sharded databases, federated servers and so on..

        • Aaron Bertrand

          But Mal, needing to distinguish between tables and views certainly doesn’t mean you need to prefix *both*. If you prefix your views (which I still don’t think is necessary in a lot of cases), then you can tell your views by the prefix, and you can tell your tables because they *don’t* have a view prefix.

          • Malathi Mahadevan

            Aaron,in theory possibly yes.But it is very difficult to have partial standards.It confuses developers and management too, either you name them all a certain way or don’t name them at all.I wish we had wise folks who listened to these arguments but in fact i would be content if they understood 3 levels of normalisation, even that is pretty tough. And in many cases it is for understanding what tables belong to what functionality and is demanded for regardless of what one may think of it otherwise.

          • https://plus.google.com/u/0/112139096945704957567/about sqlrockstar

            …and don’t forget that despite your standards in house, you end up supporting vendor apps that don’t adhere to your standards and you need to unwind all their code.

            Times like this I wish ANSI had a standard for everyone to reference.

          • ThomasLaRock

            Mal,

            I think you have offered the best summation so far. You would choose one method for designing, and (possibly) the other for supporting.

            It has made me think of a new blog post that I need to write, thanks!

          • Malathi Mahadevan

            Thank you Tom!

    • Sam

      That’s why I write all my database interface code in Fortran. It works great for me!

      Some of my coworkers hate it. I like your idea to call them “architecture astronauts” and move on.

    • karen

      I’ve posted my reasons why I hate metadata stuffing. Once you see how it’s practiced out in the wild, you realize just how crazy people can get it with. It doesn’t “just work”

  • retracement

    Good comment. I’ve recently seen more and more people of late rubbishing the use of object name prefixes as if there is a definitive law and that their opinion must be right since it is *their* opinion. The truth in the matter is that the only thing that really matters is that you code and naming standards are consistent and readable. As good as the SSMS 2012 context tips are, that is still an extra step to take when reviewing code.

    • ThomasLaRock

      Thanks Mark.

  • Gail Shaw

    Performance issue, no. However…

    If I (as a database developer) realise at some point after design once the system is already in use that I really want that table to be two tables (or three, or…), what I can do is replace the table with a view and (in theory) the application won’t notice. It shouldn’t notice anyway. However, if I’d prefixed the table name with tbl (which personally I never, never, never do), I now have either a view named tblSomething or I have to go through the code that used that table and change it everywhere. Kinda what I’d have been trying to avoid by using the view to replace the table in the first place. And yes, I have had to do this before on a number of occasions.

    On the point of DIM, FACT or similar, that’s designating the object’s purpose, not what it is. A fact table and a dimension table are both tables and there’s no tooling that could ever tell us the difference as they differ as to their purpose not their type. Just like I might likely call tables that have balances for accounts AccountBalances, not just Balances.

    Personally, I feel we need better tools (I don’t have any problems telling a view from a table because of SQLPrompt), not a bunch of prefixes.

    • ThomasLaRock

      Thanks Gail.

    • Aaron Bertrand

      Yes, refactoring can make the prefix approach difficult. Just like naming a column IntID. What happens when you need to expand to BIGINT?

      • ThomasLaRock

        Part of refactoring is the cleanup, which would imply that while the temporary step would be to have a view named ‘tbl’, at some point you would go back and remove that object as all new code would be using the new tables.

        [Of course we all know that refactoring projects rarely clean up after themselves and I will leave that blog post for a different day.]

        • Gail Shaw

          However, if I didn’t have the tbl prefix there at all, I wouldn’t need to spend ages (and it can be absolutely ages) changing and re-testing procedures and application code

          • K. Brian Kelley

            And rebuilding hundreds of reports…

      • Gail Shaw

        Or the dtSalesDate that’s actually a VARCHAR(50). Love those.

    • dcfix

      I shudder at the thought of normalizing a production table into two (or three, or …) tables, and then creating a view so that the other application layers don’t notice the change. This would be an absolute nightmare, especially if you have insert, update or delete sprocs hitting the new view.
      I would much rather make the changes in the schema and then make the changes in the code and update my unit tests.

      • Gail Shaw

        No, it’s actually dead easy. Create the view, create an instead of trigger (or 3) on the view, ensure that they’re properly written and run your tests. Unless you have some strange front end that checks to see if it is inserting into a table (yeah, had one of those), that’s just about all that needs doing.

        You can go back and refactor later, but the point is, you don’t have to.

    • Andreas Buckenhofer

      Gail, I completely agree with you. I had to maintain systems that used prefixes for tables and materialized views (in Oracle, Indexed Views in SQL Server). Some of the prefixes were already wrong as the original programmers didn’t change the prefix anymore after changing the tables into materialized views. They would have had to change the code in many tools: database, Informatica, Java code, etc.

  • Aaron Bertrand

    95% of the time when you are writing queries you don’t need to know whether it is a table or a view – you should just be assuming it’s a table unless you have reason to investigate further (e.g. there is a performance issue and maybe you are using an uber-view).

    I think a prefix on *some* objects can make sense. If you need to distinguish between views and tables, you can do that by *only* prefixing views. If it doesn’t have a view prefix, it’s a table. I see no real gain to have to type an extra three characters every time I reference Customers, Employees, or Orders. If I am going against View_Customers or vwCustomers, I know I am not working against a base table. You can also use a different schema for views, but that works against the lazy folks who don’t like to use schema prefixes.
    But in the end, you and Mark are both right: use what works, just do it consistently.

    • ThomasLaRock

      Agreed, consistency is very important here.

  • Gary Ciszewski

    Tom, thank you for this post. When I was first starting with databases I was told prefixes were outright wrong; thus I stopped because I was concerned about “doing it right,” and I hadn’t the experience then to go against what a “more experienced” user suggested. However, I’ve often felt there were times I wish I had because it just made things easier to identify objects. Call it the librarian in me that needs to organize things. At least now I have a better perspective on this. Thank you.

    • ThomasLaRock

      Gary, you are most welcome! Don’t be bullied by others into blindly following for no reason. Others have pointed out valid concerns for why they don’t use prefixes for tables, and I see their point(s), but they haven’t convinced me yet that I need to stop using prefixes altogether.

  • dreffed

    the prefixes convention I find depends on the route you took into programming and databases. Each tools will drive a convention in their tutorials and examples. Generally I find people will adopt what they see most frequently.

    The most frequent usage is… table are common and don;t need to be highlighted other db objects are used less frequently by scripters and programmers and hence need to be highlighted as different, hence the vw_, idx_, trg_ sp_ prefixes that abound.

    For some designs the difference between a view and table (views were used to enable DW switching and loads) was irrelevant in these cases there was no difference in the names.

    I think the naming convention is important but will depend on the consumers of the data, architects should be flexible in the naming and understand the db usages.

    just my 2p.

    • ThomasLaRock

      Yes, I also think the architects need to be a bit more flexible.

  • Josh Watkins

    As usual it’s a compromise situation. I’ve found that prepending “vw” to views, and no prefix on table names, is the best compromise.

    • r4vi

      exactly – why pollute the names of your tables when you’re not going to be working with views 90% of the time.

  • Pingback: Use What Works: Prefixing Database Tables With ‘tbl’ | SQLRockstar | Thomas LaRock | Itsaat()

  • otikik

    I’d rather prefix views with “v”. It’s shorter, and I use them very rarely anyway.

  • http://twitter.com/johncromartie John Cromartie

    I am speaking from ignorance here: why does it matter if it’s a table or a view? Shouldn’t a good schema remove any need to know?

    • ThomasLaRock

      I suppose so, except that since I rarely see that model being used I tend to advocate the use of a prefix. I see a LOT of vendor code that could benefit from either: using a schema or using a prefix.

  • http://twitter.com/kowboykoder Cowboy Coder

    I like prefixes myself. I also love this site layout and design. So I was enthusiastic about reading the article. However, the word “Karen” kept appearing over and over, which was a big distraction to the point of the article.

    • ThomasLaRock

      Hmmmm….I guess it is because of the sessions we have been doing and we debate this topic. I didn’t think I used her name too often, but you could be right. I’ll take better care in the future when writing.

    • Karen

      WTH? My name is fine…

      • http://twitter.com/SQLWorld SQLWorld

        It sure is.. “caring”….. oops! I meant “Karen” LOL

  • Ricky Lively

    I prefer using suffix… DataCurrent_tbl… that way I still can use accelerator keys because everything is not filed under “t”. B-)

  • Peter

    I think the argument revolves around your line of thought. Are you looking at your database conventions as ideal or practical. Ideally your names wouldn’t be prefixed so that in the event you need/want to swap a Table for a View you can do a quick rename of the table and creation of a view with the former name. Bing Bang Boom! No need to update other objects to use the new name (e.g. Views, Triggers, Stored Procedures, User Defined Functions). Realistically though you probably won’t have to do this for the majority of the databases you create in your life and knowing at a glance if you’re working with a table or a view is probably more valuable in the long run.

  • http://twitter.com/thedouglane Doug Lane

    I prefer to keep prefixes out of my design with the exception of using v in front of view names. I do this because:

    1) They’re similar in purpose (meaning I query them)
    2) They’re the most frequently referenced objects in my code
    3) I’ll often use the same name for the denormalized view version of the table (e.g., Customer -> vCustomer)

    I don’t run into these issues with other objects, so I don’t worry about prefixes anywhere else.

  • Karen Lopez

    My rebuttal is live. I think we aren’t really that far apart. Except for your insistence of prefixing every object in the database to be consistent.

    http://blog.infoadvisors.com/index.php/2012/10/10/metadata-stuffing-why-i-hate-tbl_-for-table-names/

  • Allen White

    I came here to explain the exact use case that Gail described significantly better than I could. Thank you Gail, and Tom, that is the exact scenario I explain in classes as to why you should never use type prefixes for object names. They change, and then the names are misleading, because it’s too difficult to properly refactor the database. If you understand true relational theory you’ll know that fundamentally tables and views are in fact just virtual representations anyway.

    • Karen Lopez

      I think “never” is too strong of a word. My “splendid truth” (i need to blog about that) is cost, benefit and risk is how we should decide.

      I have to support shops that have only native DB2 tools. Those tools are still mired in the mainframe text only world. They still truncate names for presentation purposes. You have to write a query of the system catalog to get meta data about an object…its so flipping painful.

      I make lots of concessions to stuffing names for those guys. But the “right” solution would be to get some real tools to work with a modern database.

    • ThomasLaRock

      Allen, are you suggesting the the use of prefixes makes refactoring difficult? To me, refactoring is the process that is employed when you need to make the changes that Gail has described. The use of prefixes are not going to be the deciding factor for whether or not a refactoring effort will be successful.

      I am quite aware that tables and views are logical representations of data pages physically residing in one (or more) files on disk.

      When a developer decides to join five views together without understanding that they are really joining 25 tables together, and performance is bad, then degrades over time, and I get called to fix things and I have to say “well, looks like you need to change your code, your design, or your hardware” and they get angry because I can’t find the Turbo button quick enough…that is when I wish it was easier for people to understand which logical representation they were using when building whatever solution they were tasked with.

      Should it matter? Nope, not at all.

      Does it matter? Yeah, almost always.

      • Allen White

        Yes, it matters, but the names don’t make a difference. So it takes you an extra 10 seconds to figure out that the object they’re working with is a ‘view’ versus a ‘table’. Is it really worth forcing a refactor when you realize that you need to convert a ‘table’ to a ‘view’? It’s not that hard to figure out. It’s cleaner without the prefixes – let the object name represent what the object is. As the DBA you can change the underlying structures as necessary to get the performance required, but it’s a lot harder if you have to change tblWidget to vwWidget as well.

        • ThomasLaRock

          Thanks Allen, I look forward to you convincing me further this weekend in Nashville.

        • http://twitter.com/sqlpto Pedro Azevedo Lopes

          Have you considered that *sometimes* people (that’s me a lot of times) are sent code for offline review before going onsite. I like to be able to pinpoint what would be the potential weak spots beforehand.
          Just like Thomas said, 5 views can be expanded to 25 tables (not going in to specifics in to types of views vs. sql editions), and that is something I want to get straight in.

          • Daniel Liuzzi

            In that case you also have to consider that “tblCustomer” might actually be a view, because the guy who made the refactoring was too lazy to update the prefix.

            If you’re doing offline review without proper CREATE scripts for the objects you’re reviewing, blindly trusting a prefix, you are risking the accuracy of your review.

          • ThomasLaRock

            Yep…refactoring…and failing to clean up properly afterwards is a headache for lots of reasons.

  • http://www.facebook.com/rojipt Roji P Thomas

    What about synonyms? Do you also suggest that they should be named synonym_Something or they should inherit the prefix of their original object name?
    Personally I think that the Programmability layer should not make any assumption about the physical representation of the object being queried and just consider them as logical relational entities.

  • Chris Yates

    Wow! Thomas very nice well written article. I’ve read the comments thus far and this is really a great conversational topic. I myself do not use table prefixes, but follow the same concept that Josh Watkins stated ( I leave my tables without prefixes but I do prefix my views). For me it is a Pre-standing standard at my current employer and one I’m accustomed to for various reasons; if it wasn’t a standard though I’m not sure I would include a prefix anyways. I think the last paragraph you made sum’s up my stance on it, “Using prefixes still works for me. It also works for others. It may not be your choice, but that does not make it the wrong choice for others”. Just because I might not go the route you take or others take I’m always open to hear other perspectives from fellow DBA’s and the community. It’s healthy to have other viewpoints. Thanks for the post and for what it’s worth to Karen I don’t mind her name being mentioned. Have a good one.

    • ThomasLaRock

      Thanks Chris!

  • http://www.sqlstudies.com/ Kenneth Fisher

    Just out of curiosity why haven’t I noticed anyone mention the use of prefixes on stored procedures and functions? To the best of my knowledge most people agree that you should use prefixes of one type or another for these types of objects. For that matter constraints and keys usually have some type of prefix. Is there really that big a difference between usp_ and vw_? Or pk_ and tbl_? I understand the refactoring argument might be stronger for tables and views than for other types of objects but I think being consistent is also as important if not more so.

    • ThomasLaRock

      Kenneth,

      Yes, that is part of the debate as well.

  • Alex Dess

    I’m not a fan of the prefix “tbl” because it means three extra letters I need to type in. I use SQL Prompt and I want my table names to be selective so I don’t have to type more than I have to. Then again I’m lazy :)

  • http://twitter.com/spaghettidba Gianluca Sartori

    I completely agree with Gail. I never, never, never prefix object names, because they can change. A view named tblSomething is not fun.
    However, even before SSMS had that useful tooltip (it was there in 2008R2 BTW), you had ALT+F1 to run sp_help.
    I’m with Karen here: you need to try harder. And not as hard as may think.

  • Pingback: Something for the Weekend - SQL Server Links 12/10/12()

  • http://twitter.com/SqlServerNerd Brandon Leach

    As long as you are using the same naming conventions consistently across your various databases and server, then I think its really just a matter of preference.

  • sqltucker

    Advocating for use of prefixes: As developers we often forget that we are not the only ones who will be looking at our code. Yes it is a pain in the neck to change object types with prefixing (and let’s not start on the complexity of changing when you prefix datatypes).

    But don’t forget about our downstream. Eventually (if we’re doing it right) we aren’t the ones supporting the products by our code. If we’ve done our job, we’ve put in enough error handling testing etc etc. that our code can now be called a “Product” and can be supported by the ever so important Generalist.

    A generalist is an invaluable asset and if we do our jobs, the first line support that they can provide will generate praise and satisfaction from our customers. Little things like prefixes will enable a generalist to understand where data might be and will help them understand a little bit of the design and data flows. This will go a very long way in diagnosing a perfomance issue, invalid data displayed in the app, a runaway query etc. Remember, many times our generalists are on customer sites and don’t have access to the same tools as us. A little (sometimes alot) of extra effort on our part is very big difference in the customer’s experience.

    We can enable our downstream recipients by carrying some (most) of the the burden. During development and during design we should always be thinking about what it’s going to take to support what we build.

  • http://twitter.com/sqlpto Pedro Azevedo Lopes

    Although I still agree with your original statement. I like to be able to read offline code and just know from the objects name what it is: tbl for table, vw for views, ufn for functions, usp for sprocs, etc.
    Forgive me for being a sucker for standardization. :-)

  • Pingback: What’s that object? | Base Table()