
Use What Works: Prefixing Database Tables With 'tbl' - kitty
http://thomaslarock.com/2012/10/use-what-works-prefixing-database-tables-with-tbl/
======
zzzeek
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...](http://dev.mysql.com/doc/refman/5.0/en/identifier-case-
sensitivity.html)) 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.

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

~~~
zzzeek
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" ?

~~~
pyre
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."

------
bunderbunder
> 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.

------
yummyfajitas
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.

~~~
naelyn
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

~~~
bunderbunder
> 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.

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

SELECT COUNT(*) FROM tblposts

~~~
pradocchia
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.

~~~
naelyn
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)

~~~
pradocchia
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.

------
jasonkostempski
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 :)

------
drsim
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).

------
jhaywood
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.

------
borlak
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).

~~~
jcoby
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.

------
DrJokepu
adjHungarian nounNotation verbHurts detThe nounReadability prepOf possYour
nounCode.

~~~
SQLRockstar
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.

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

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

------
mgkimsal
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.

------
armored_mammal
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.

~~~
SQLRockstar
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.

------
axlerunner
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. :)

~~~
SQLRockstar
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.

~~~
emp_
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.

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

------
MSM
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.

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

...and ditched the "rockstar" bit.

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

------
pradocchia
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.

------
mongol
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.

~~~
adamzochowski
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.

