Metadata Stuffing: Why I Hate tbl_ for Table Names

Oct 10, 2012   //   by Karen Lopez   //   Blog, Data, Data Modeling, Database, Snark, SQL Server  //  12 Comments

 

Karen Lopez Quote Table and View

 

This week was Canadian Thanksgiving so this post on metadata stuffing is timely.

Today Thomas LaRock (@sqlrockstar | blog ) posted a rant about our Database Design Throwdown topic on naming standards for tables and other database objects.  Tom is a fan of what I call “metadata stuffing” in object names.  That’s basically shoving as much additional information as one can into object names so that one does not have to go find out that information from its rightful place.

My reason for wanting to use prefixes is simple enough: I want to know if I am looking at a table or a view when reviewing code.

Karen doesn’t believe that anyone should be using object names as a place to store meta data about the objects themselves. I would like to agree with her but then we’d both be wrong.

He even created the nice graphic of my quote I use here. Thanks, Tom.

Object Confusion Abounds

That quote is indeed one I use during our debate.  It’s a snarky introduction to what I think the real problem is: our tools aren’t doing enough to help us with this potential confusion of tables and views.    When you are writing or looking at a query, the syntax for referencing a table or a view is exactly the same.

When a developer writes:

USE AdventureWorks;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product 
ORDER BY Name ASC;
GO

He has no idea for certain if Product is a table or a view.  The syntax is the same.  And if the developer is working in a text editor of some sort, or with some native tools, there’s no tooltip or other help that the he can use to check what type of object they are querying.

Why does this matter?  Tom has a great presentation where he shows the impacts of trying to make stuff work and you don’t know what you are looking at.  That’s why Tom wants to do this with his objects:

FROM Production.tbl_Product


or

FROM Production.view_Product

So that the object type is injected into the object name.  That seems so innocuous, doesn’t it?  What could go wrong?

I’m here to tell you that this is a slippery slope.  One of the most egregious examples of this sort of meta data stuffing I’ve run across is one that required all this meta data to be prefixed in front of every table name:

  • tbl_ prefix
  • Primary Systems that managed the table
  • Primary subject area that the table belongs to in the data model
  • Classification of the role the table plays in the database (Associative Entity, Domain Entity, Master Data, Reference Data, Log Data, etc.)
  • Three letter login of the DBA responsible for administering this table (I KID YOU NOT).

So in this wonderful naming scheme, we’d get:

FROM Production.tbl_MFGR_ORDERPROC_DOM_KQL_Product

I have found that once an organization starts thinking of stuffing, their designs become turkeys really fast.  It’s ugly.  Think about the tools you use, with all those nifty object lists on the left side. To find a table you need to know all that great metadata as you scroll through the list, hoping that PRODUCT Is buried in there, somewhere.  And what the heck was the name of that guy that dressed funny who did all that data stuff for the company before he won the lottery?

All that metadata that should have been managed elsewhere, not prefixed in front of the “real” name of the table.  In fact, it was.  In the data model and in the system catalog. Every time any of that data changed (DBA assignment changes, DBA wins the lottery, whatever), we had to rename the table and change all the code and reports that referenced it.  Sure we could have isolated systems by this change by using views and or aliases but that is additional complexity for no performance gain, either.  Refactoring might have helped, but eventually we’d still have to change all the code and queries.

Why I Hate Metadata Stuffing

  1. It’s redundant data.  Just like with business data, the reason we want to minimize redundant data is because we then have to worry about updating the data in multiple places. There’s cost and added risk for that.
  2. It changes.  I don’t know about you, but I don’t have the luxury of taking down a production system just to update  a change in the name of the DBA’s favourite TV show or whatever lame naming scheme someone thought up. Sure, tables can’t change into views or indexes, but all the other type of stuffings will change.
  3. It takes up real estate. I get all kinds of flack from developers and DBAs for the length of object names when I want the names to be meaningful.  It’s funny how spelling out CUSTOMER is unacceptable, but adding the exact same characters in front of every object of its type is A-OKAY.  What’s up with that?  Somehow optimizing names for developers is more important than loving your data?  Show me where it says that in the Project Charter.
  4. tbl_ is a tell for bad database design.  I don’t know where this particular naming scheme originated, but when I do a database design review and I see this naming scheme, I know that the designer learned design in a one hour webinar “training course” and has not really mastered the complexities of enterprise database design and maintenance. The design will be less than best practice 90% of the time.  This naming scheme is prominent in programming books, introduction to database books, presentations by non-database people, and uninformed blog posts, by far.  It’s not popular with people who do professional database design. Sure, some products use this, too, but do you really want to take database design best practices from vendors? How many professional data architects do you think they have on staff?  I will most likely see a database design that is highly optimized to make development go faster.  Not for data integrity or loving data.
  5. It’s not needed “for consistency”. One of Tom’s points is that if we are going to prefix views, we have to prefix tables to be consistent.  Actually, no, we don’t.   If we have to bite the bullet and prefix views because our tools let us down, we can choose not to clutter up tables names just to punish those objects, too.  I’m assuming that since Tom prefixes tables and views, he prefixes columns, too, right?  its just being consistent.  < snort>
  6. It gets in the way of using the data. Tables and Column names are the most user-facing parts of a database design.  When we in IT insist on munging up these names with a bunch of systemese, we make it more difficult for business users to get at their data.  It shows that we have optimized the database design to help a relatively small number of technical users (developers, DBAs, ETL folks) over the needs of the business.  Ultimately, we build databases to manage data.  For the business.

What’s the Cost, Benefit and Risk?

One of my Splendid Truths is that all design decisions should assess cost, benefit and risks.  In the overall scheme of things, just prefixing “tbl_” in front of a table name isn’t that costly and it isn’t that risky.  Tom assesses his designs based only on potential for performance harm according to his post.  He “laughs” at my position.  I’m happy that my stance on metadata stuffing brings happiness to his day.  But performance is only one data point out of many for making a design decision.  Usability, clarity, business goal support are other factors that a database architect needs to consider when assigning a name to an object.  If we optimize something for a subsystem, we do it as the expense of other subsystems.

Our Tools Should Help Us More

Select statement view hover tooltip

Having said that, I feel the pain of people having to work with sub-standard tools or having to use tools that just refuse to help.  Tom showed how SQL Server Management Studio tooltips can help.  But all those command line “I don’t need any stinking help” aficionados are left on their own to know what they are looking at.

Oh, and Tom:

ProperNoun_Tom, Pronoun_you Verb_made Pronoun_me Verb_laugh Preposition_with Possessive_your Noun_post.

(See how all this stuffing gets in the way?)

One of my other Splendid Truths about database design is:

Your tools will impact your data models and database designs more than you can imagine.

We shouldn’t sit back and let that happen. Stuffing is great with Tofurky, not with databases.

12 Comments

  • SELECT * FROM tbl_MSAccess_wannabe_DBAs

  • Karen- Excellent point about USING the data. When presenting data to end-users for reporting, it’s certainly been useful to pull stuff together for them via a view, and just present it as another table that they can query. What’s to be gained for them by labeling specifically as a table or view?

    • Users typically don’t care. The people who care are the people who are firefighting system outages, performance snafus, data issues. That’s important. But I don’t feel the munging of database names for that narrow use case, as difficult as their job is, is worth the overhead and clarity issues for end users.

      That’s why I can tolerate vCustomers, but not Tbl_Customers as much. I know Tom thinks that’s inconsistent, but I don’t think so. It’s funny that set theory isn’t clear here.

  • I have read both your posts and like/agree with both of you.I think the answer is just that it depends -if i am the designer and i have the freedom to design and name something as I wish then I’d gladly follow your norms. But if i am supporting something someone else designed, or if i am forced to follow some standards someone else wrote for the sake of consistency or any such thing then i really can’t. I get your point on trying harder,but i need time.When someone is waiting on you and can’t afford to give you time then i’d rather have a ‘tbl’ and ‘vw’.I really have given estimates for what i thought was a five table join and ended up with 50 views and 200 tables. High level Database refactoring and designing from scratch are wonderful opportunities to implement all this but they are few and hard to come by , that is why one is forced to take a more pragmatic stance.

  • I’m pretty sure I’ve change views to tables and tables to views. Mandated prefixes were annoying in those cases.

    • I don’t that’s physically possible – not a direct change. Logically, of course it is. In other words, you can’t ALTER a Table to be View.

  • You are probably not a fan of Hungarian notation naming conventions for columns or is that entirely unrelated?

    • I am not a fan of the Hungarian notation for columns

      I am currently working in a system that is doing this stuffing all over the place (developed by outside developers unfortunately). On top of the table indications in the table name all columns start with an F followed one character indicating the type. And as bad as it is (readability being one of the issues) its not consistently followed – with the worst offender being FD which is supposed to be a date column is in several instances defined as a varchar column. There is also the case of FI and FN being integer and numeric respectively – often the underlying type is either.

      One of the tenants of information theory is that the more a value is repeated – the less information it conveys. All columns start with an F – so what does that tell you? Precisely nothing since everything starts that way. Take away the F and you have the type preceding the column name – it tells you only a little bit more and that is dependent on its correctness. Add to that if something has to change (for whatever reason zip codes were represented as integers and had to be redefined as character columns) the amount of code that has to be touched can be problematic at best.

      This is all a throw back to early Windows programming and the Petzold book (and if you remember that book that will be showing your age)

      • Wow. That sounds awful.

        I work with an industry standard data model that has just as bad of a naming convention. The first thing I do when I tailor is is rename all the physical objects to have modern human readable names.

        It’s so much a better product this way.

  • We do mostly read-only queries on a static set of data for data validation. Sometimes we have to change views to a table (by renaming the view, re-creating the structure as a table, and then copying the view to the new table) due to performance reasons. It would be a nightmare to then go modify all other code to deal with the object type switch. Instead, it takes about 2 minutes to set up.

  • […] If you’re curious about the debate, here’s the original blog post from Karen in 2012. […]
    » OT: Bad Tools Lead to Overly Verbose Code? recently posted..timberland zalandoMy Profile

  • Thanks for the recommendation to read the blog, Karen.

    First, what the DBA technical team wants, the DBA technical team gets. If they dictate the offending object showing up on an error log shall be immediately recognizable as to the owning schema & object type, it shall be done regardless of possible redundancy.

    I’m not sure I’ve ever changed a view to a table or vice versa, or even why you’d want to. Adequate requirements-gathering, script performance, & access frequency should make it quite clear which it should be, shouldn’t it?

    Physical names are not terribly important to our end users who access data via ‘Masters’ (part of the IBI FOCUS tool) data admin generates from the meta data repository. As long as DA has documented the business-dictated logical names, users never see nor have any interest in the physical names of implemented objects. We do have power users, of course, but they’re usually the ones who provided the logical side to document in the first place.

    We use ERwin to model when DA designs the system, & to slurp models into when DA doesn’t do any of the design but is charged with QA’ing the design; DA also generates DDL from ERwin. DBAT rarely, if ever, goes into ERwin or the meta data repository for anything, & relies on DA to provide that information when requested. When all the physical storage is added to the DDL by a DBA tech, we scoop it from the implemented DDL & compare it back into the ERwin model.

    Lastly, who in the world would add a person’s identification to the name of an object?! Guess like a lot of interesting — if bizarre or unworkable — ideas, it must’ve sounded good at the time.

    Again, thanks for the recommendation. j

Leave a comment

Subscribe via E-mail

Use the link below to receive posts via e-mail. Unsubscribe at any time. Subscribe to www.datamodel.com by Email


Recent Comments

Categories

Archive

UA-52726617-1