Use What Works: Prefixing Database Tables With 'tbl' | Hacker News Reader

Use What Works: Prefixing Database Tables With 'tbl'

thomaslarock.com

27 pointskitty43 comments

Seeing a big MySQL schema where all the tables look like "tblUsers", "tblAccounts", etc. typically lights up the amateur lights for me - because pretty much when I see that, only about 58% of the tables will actually be named that way. Another 32% will be the other way, "accountsTable", "profileTable"; 10% will not have prefixes or suffixes; about 60% of the tables will be named with a plural name (i.e. "users") and 40% will be singular ("datatype"). Foreign keys being present is pretty much a 50/50 crapshoot (and in the 50% case, it's a further 50/50 chance that they actually knew to use InnoDB so that those FKs have any impact). CamelCase and lowercase_with_underscores will be gratuitously mixed, and MySQL's platform-dependent handling of casing conventions (http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensi...) will go a long way towards making the semi-camelCase application, which probably doesn't quote identifier names (or does so inconsistently; all the SQL is hardcoded), non-portable.

Do you have anything to say about the practice itself, rather than simply listing a lot of other practices you have associated with it?

The collection of names that you set up for "relations" (in the Codd sense) in a schema is the primary "surface" of that schema. Whether they are tables, views, or synoynms to those (i.e. Oracle), each of these names presents the same interface - a collection of rows. Prefixing or suffixing these names detracts from the perspective of the database as a collection of relations, it gets in the way and makes it more difficult to make names interchangeable. The "tbl" prefix is a defensive decision that is unnecessary and detracts from the notion of the schema as a collection of relations.

It is more common to have qualification for objects that aren't collections of rows, like sequences, constraints, and indexes. These are qualifications like "fk_", "pk_", "uniq_", etc. and they serve the purpose of being able to distinguish between "relations", which are the primary API of the database, and "supporting" constructs. These names need to be distinguished from table names as well as from each other and with the exception of sequences are also not present in SQL statements, only DDL.

Consider why none of the other names in relational databases are prefixed as to their type. We all use functions like "current_timestamp", "count", keywords like "CAST", fixed system tables and views like "pg_catalog". Why aren't these named "fnCurrentTimestamp", "fnCount", "keywordCAST", "tblPgCatalog" ?

We suffix views with '_vw' and materialized views with '_mv'. This makes it easy to not have to consult the DDL every time we need to know what a table is. I've also run into several instances of:

  object_name_mv
  object_name_vw

I can only assume that at some point the view was non-performant for user-facing application and was turned into a snapshot.

In general, I'm a fan of suffixing views with '_vw' to basically be a red flag saying "there's another query behind me."

You have kind of contradicted yourself. "Seeing a big schema where all the tables look like ..." and "only about 58% of the tables will actually be named that way..."

So which is it? "All the tables" or "58%".

What you're trying to say is that inconsistency lights up the amateurs lights.

> Even data warehouses will use “Dim” and “Fact”.

A practice whose relationship to tibbling is mostly cosmetic. Dim and fact are prefixes that are used to indicate additional semantics about the table's purpose that can't be determined by inspecting the object itself - in this case, whether the table represents a dimension or a fact.

Tibbling, on the other hand, doesn't do much more than harm maintainability in the long run. Consider what happens if, say, you ever need to split a table into two different ones. You probably don't want to have to go and rewrite all the queries that referenced that old table. No problem, just create a view that joins the two new tables and give it the same name as the old table. Now everything that referenced the old table will still work fine.

Except if you tibbled the table name. If you did that, then you still need to go track down and modify every single object that references the table. That, or you'll be resolved to having a view whose name starts with 'tbl' in your database. In which case your tibbling scheme has been torpedoed, because the presence of erroneously prefixed objects means you can no longer trust any of the prefixes.

There is actually a very good reason NOT to do this.

At some point you might need to denormalize, replacing a view with a materialized view. Now you either need to change every instance in your code, or you need to have a table prefixed with "vw". (Similar troubles apply if you replace a table with a view.)

The entire purpose of views is that from the perspective of the client, it doesn't matter whether it's a table or a view.

...or, I could use "mvw" to designate a new type of object...

I agree, the client shouldn't care about the data being a table or a view. But as a DBA, if I need to quickly find a solution to a problem, the use of a prefix can be a benefit.

There are costs and risks for all design choices. If you are working with a system that is growing and prone to the need of denormalizing frequently then perhaps using a prefix isn't the right choice.

But not every system has that issue. I see more cases these days of smaller databases...think "one database per customer" type of architecture. Denormalization isn't an issue often, and prefixes seem to work just fine.

I don't agree with prefixing tables with "tbl" to distinguish them from views, but I do agree with the general notion of using the prefix for any "table-like" entity in a database.

If you work with a growing codebase that slowly falls into the (popular) antipattern of DatabaseAsIntegrationPoint, you will end up with dozens of programs spread out over multiple repositories all interacting with some shared tables (not ideal, but it happens).

If you named your tables something like "posts" then I _guarantee_ that if you grepped your entire repository for "posts" that you are going to find an awful lot of false positives in variables and class names. OTOH if you named it "tblposts" then it's far more likely to be a globally unique string identifier.

Why would you need to grep the codebase for a table name you ask?

* prerequisite to a non-additive table ALTER that might have unintended side effects

* prerequisite to trying to undo the carnage of DatabaseAsIntegrationPoint

> If you named your tables something like "posts" then I _guarantee_ that if you grepped your entire repository for "posts"

You shouldn't have to do that in a well-factored appliation, because you're using stored procedures instead of inline SQL.

So you're recommending writing a stored procedure for this?

SELECT COUNT(*) FROM tblposts

Yes. Or alternately, creating a set of views to alias the base tables, and only allowing direct access to the views.

Either way gets you some degree of surface area management. Procedures have some added benefit--it's a lot easier to inspect the flow of data when everything is routed through procedure calls. It's a lot easier to put that flow in context when you have a procedure name as a label, provided that your procedures implement a batchful interface.

Coupling queries to the database (stored procedures) instead of to the code that uses them (inlined queries) seems like a recipe for deployment headaches. Especially if you're deploying new code (with new queries) every 2-4 days without downtime.

This approach only makes sense in one of two situations:

1. Ivory Tower DBAs run your company and tell developers "no" at every turn. (sad)

2. Your engineering team makes changing queries hard because they can't hire any developers who know anything about your underlying database platform internals. (also sad)

There's two huge reasons to keep procedures in the database, neither of which has anything to do with ivory towers. They're really about the most basic tenets of good coding practice:

1. Maintainability. It's necessary to put your SQL on the server if you want to keep it well factored. Just like for any other language, oft-repeated bits of SQL code should be factored out into separate procedures and functions. If you're relying on inline SQL, you're forced to choose between habitually violating the DRY principle or resorting to an unmaintainable mishmash of server-side and client-side queries.

2. Testability. The good unit testing frameworks for SQL code are written in SQL, and designed to be used from an SQL development environment (i.e., the database). And just like for any other language, your SQL code should be covered by good tests.

There are plenty of tools out there to help with deployment if it's causing difficulties for you. I recommend using them if that's what it takes for you to be comfortable with the platform.

I try to be the guy who interops between the reactionary DBAs and the happy-go-lucky developers--two parties that optimize for different ends.

So most recently, we had an app that started off with direct table access via an ORM. Once the data access paths stabilized somewhat, I started replacing them with stored procedures. Those stored procedures gradually coalesced to form an API. The ORM-like functionality is still there, if need be, but the stored procedures now provide a contract, much like a service.

In retrospect, I'm not sure the ORM was even that useful. Besides encouraging certain bad habits on the consumer side (eg, most instances of lazy loading), its one more level of indirection to grapple with. Why not drop down to the database and write your implementation there? It can be tested right there and then, and directly in terms of the data flow: input -> output.

What about when you're not reviewing code or it simply doesn't matter which it is? In my experience that's most of the time. It's just something that's going to return data in a tabular fashion. I'd rather have a clean name all the time and have a little frustration sometimes. Views and stored procedures likely have more detailed and predictable names anyway, 'People' is a table, 'PeopleWithResidentCounty' is a view, 'GetPersonById' is a stored procedure. The practice also hurts what little support SMSS has for quick searching the db object list, instead of staring to type 'Pe' for 'People', I have to type 'tblPe' quickly before the timeout. Also, if 1 person breaks the convention or misunderstands exactly how to use it and it ends up in production, the whole plan falls apart, worse if some code expects the convention to be used a certain way. It's obviously not about machine performance issues, at that level It's about other developers perceptions. If it's just going to be you working on it, fine, whatever, but if you want to collaborate, the practice will cause many developers to question the quality of the entire project and it might reduce their enthusiasm for working on it at all. Note, I have to work on a system using this convention a lot, so it's a bit of a sore point for me, especially since I enthusiastically helped reinforce the practice many years ago :)

I dislike the prefix from a stylistic point of view only, the arguments for and against are weak.

I remember debating with our datawarehouse designer whether our tables should be singularly-named or plural. Also if the primary key should be TablenameID or just ID.

In the end it doesn't matter. The only thing that materially impacts productivity is maintaining consistent conventions. Take it from someone currently working on a database with three different object prefix styles, key naming and data access methods (ORM, stored procedures and argh dynamic SQL).

While at the end of the day being consistent is always the most important thing, I don't agree with prefixes in database names. If you're not going to be accessing the data using stored procedures your table names are part of the API for your data. Don't reveal your implementation details in your API don't depend on those details in your application. Why forget all the good stuff you know about encapsulation just because the database is involved.

A place I worked did something that was at first extremely annoying, but later made a lot of sense -- prefix all the column names of the table with the table name.

So for a table 'user' you would have: user_id, user_name, user_password, etc. Some tables would have ridiculous long column names.

So what is this good for? First off, joins. If you had a 'post' table and a foreign key back to user_id, the naming scheme was: post_userid. So a join would be: "select from post inner join user on user_id = post_userid". There is no need to alias either table. Also, if both tables have the same field, say both have a 'note' field, it is clear which note you are accessing and there are no ambiguous issues (since one table is post_note and the other is user_note).

Maybe I'm missing something but how is post_userid better than post.userid? It's the same length, harder to type, and clutters up your table definitions.

I've been through all of these naming schemas over the years (including the OP's tbl prefix and your table_ column prefix) and honestly they don't help. At best they disambiguate a corner case and make for a lot more typing than is needed. At worst you end up with things out of sync and you have a view named tblFoo with a column named post_blah because you don't want to mess up something that was coded a year ago and needs to keep working.

Keep things simple, format your queries, be consistent and all will work about as well as it's going to work. SQL is ugly.

That doesn't sound like a bad system to me, but I think I achieve the same clarity by writing my queries in a more explicit way. I always use multi-part identifiers. For instance, I would join "ON user.id = post.user_id".

I have a love-hate relationship w/ column prefixes like you describe. One on hand, they make searching the codebase much easier, and if you can search the codebase, you can refactor w/ confidence.

On the other hand, it bakes the schema into every single column reference, and that makes schema changes more costly--either you cruft up your database with now-misnamed columns, or you fix all references, or you avoid changes in the first place, and the business drifts further and further from the database model.

My compromise position at places that do use column prefixes has been, column prefixes on base tables, views and/or stored procedures for client access, and no prefixes exposed to the client.

adjHungarian nounNotation verbHurts detThe nounReadability prepOf possYour nounCode.

Cute, but those words have meaning properties on their own. Your example is not code, and so doesn't necessarily provide insight into how prefixes may affect groking code.

He's talking about naming things (one of the two hard problems), where what the thing is becomes more readable to him with the prefix on the name.

Only when you don't use prefixes that are somewhat standard.

At the end of the day, however, consistency is key. For example, you used a capital letter to distinguish the start of a word, making it easier for me to understand what you were saying.

I don't see how capitalizing all words makes it easier to understand English sentences (Can You Hear Me Now)?

Your parent comment is referring to the use of camelCase when smashing words together, not to capitalizing every word of a sentence.

I've been a hungarianNotation hater for several years. I despise the codecademy sections which require variable names use this convention.

I'd largely agree with you with regard to Systems Hungarian Notation, but Apps Hungarian Notation has made my job much easier when talking about whether data is sanitized/unsanitized (or other similar distinctions that fall under the same type) in the scope of a larger program. In that case, it can prevent programmers from mistakenly interpreting the state of the variable, which is much harder to easily glean than the type.

Perhaps I should start prefixing all my files with file_ as well, so when I'm looking at stuff on the file system, I'll know it's a file vs a symlink. I'll always know when I sort by filename to start looking under "f" if I want a file.

What's interesting about the "use what works" mantra invoked here is it's not saying who this should work for. A database is a resource that's typically shared between multiple parties, and enforcing one somewhat arbitrary rule for the convenience of one party to the detriment of another party doesn't sound very amicable.

I think there's a special level of hell for people who prefix their tables with tbl. They share it with people who give their schemas plural names.

At best it's the obnoxious curry code special.

I think there is a special level of hell for people who use nested views across linked servers hundreds of miles apart and demand that the DBA team give them sub-second response times despite limitations put upon them by things like the speed of light.

Wow. How much precious time have you wasted having to type tbl as a prefix every time you reference a table in your code or during maintenance? Also, how about the mental energy needed to filter out the tbl prefix whenever looking at a list of tables? And now you've wasted a minute of my life having to comment on this issue. :)

Fair point. But how much time have I wasted trying to root out a performance problem caused by nested views disguising themselves as tables?

The tools have been failing us. Using a prefix is a way to get around those failings.

This is the same as the "don't use aliases but instead tablename_id for Id fields because at 3am this will help you" type of argument (a real one, unfortunately) you should not model things around exceptions.

and... if debugging live code on production machines at 3am is the norm, you've got much bigger problems than table prefixes.

Is that really an issue?

Let's say you have identified a performance problem on a single page of a web application that lists results from a query.

Let's say you look at the code and see a complex query.

What do you do?

You use the equivalent of EXPLAIN on your DB.

It should be pretty clear at that point what objects you are dealing with.

The point of a view is that it should be interchangeable with a table logically. There are many cases I've been involved in where a view was used to either temporarily address a performance issue or address a data migration need.

If you have a naming standard that requires objects to be named a certain way, you're going to have to do a code push along with a database change that would otherwise only require a database change. That's a lot of extra testing and a lot of extra risk.

Either that or you are going to temporarily break your own rules just for that one thing. But now guess what, you've created an even bigger problem because you have trained everyone to not look at the EXPLAIN plan and instead rely on the names of the objects, and so now they'll be really confused because you've temporarily made a view "look" like a table.

In practice, this is a solution to a non-issue. YMMV.

To me the issue is that the most important thing about a database is the data. If you're reviewing code with a join to an unknown table or view, will a "tbl" in front of the object really swing your vote between "This is passable code" and "I better dig deeper"?

If I'm looking at a join I need to make sure that the data is how I'd expect- is it 1..1, 1..many? If I don't have a good understanding of the object and the data it contains, I don't care if it has a prefix, I'm going to dig deeper. If I dig deeper I'm going to remember what that object represents and I won't need a prefix in the future.

So what exactly is the use case of the "tbl" prefix? Are you skimming over unknown objects just because they have a prefix? I don't see any upside.

I'd probably take this more seriously if the author legally changed his name to "PersonThomasLaRock".

...and ditched the "rockstar" bit.

I would be more convinced if he wrote why is it important for him to include a prefix. What's the value in it?

I'm sympathetic to this argument, to a point.

For database code, executing in the database (ie, stored procedures and the like), table prefixing does make the code more searchable, and reduces the amount of context one needs to grok a single block of code. When you need to refactor, you can quickly find all references.

But as a public face to clients, I find that tbl-prefixing exposes too much implementation detail. Tables and views are both relations, and there's a continuum from physical relation to virtual relation. If I use the same naming convention for both, I can keep my public API constant while iterating on the physical schema. This is very useful to me. Consequently, within the API, I try to keep views simple, and write them to provide table-like performance.

My reason for prefixing: makes it possible to use a table called Order (plus prefixing, naturally). Orders are a quite common thing to have in databases.

1) you can use plurals, so "select from orders"

2) typically there is no single order table. Most systems will have order_summary / order_details / order_items / order_packages . Think of handling a single order that has multiple products each of multiple quantities. And to complicate, fulfillment of product quantity requires multiple warehouses / shipments.

3) you can have a table called order, you just have to ensure to escape it properly, either double quotes or square brackets. Similarly table names can have spaces in them.

Reply on news.ycombinator.com