Use What Works: Prefixing Database Tables With 'tbl' • Hacker News Board

Re: Use What Works: Prefixing Database Tables With 'tbl'

#11

Post by numbsafari » Wed, Oct 10, 2012, 3:00 PM UTC

SQLRockstar wrote: post #4

axlerunner wrote: post #3

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.

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.

Re: Use What Works: Prefixing Database Tables With 'tbl'

#12

Post by SQLRockstar » Wed, Oct 10, 2012, 3:00 PM UTC

yummyfajitas wrote: post #6

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

Re: Use What Works: Prefixing Database Tables With 'tbl'

#13

Post by naelyn » Wed, Oct 10, 2012, 3:00 PM UTC

yummyfajitas wrote: post #6

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…

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

Re: Use What Works: Prefixing Database Tables With 'tbl'

#14

Post by SQLRockstar » Wed, Oct 10, 2012, 3:02 PM UTC

armored_mammal wrote: post #9

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.

Re: Use What Works: Prefixing Database Tables With 'tbl'

#15

Post by bunderbunder » Wed, Oct 10, 2012, 3:03 PM UTC

naelyn wrote: post #13

yummyfajitas wrote: post #6

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…

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…

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

Re: Use What Works: Prefixing Database Tables With 'tbl'

#16

Post by viraptor » Wed, Oct 10, 2012, 3:06 PM UTC

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

Re: Use What Works: Prefixing Database Tables With 'tbl'

#17

Post by naelyn » Wed, Oct 10, 2012, 3:06 PM UTC

bunderbunder wrote: post #15

naelyn wrote: post #13

Earlier quoted context omitted.

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…

> 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

Re: Use What Works: Prefixing Database Tables With 'tbl'

#18

Post by slurgfest » Wed, Oct 10, 2012, 3:24 PM UTC

SQLRockstar wrote: post #7

DrJokepu wrote: post #2

adjHungarian nounNotation verbHurts detThe nounReadability prepOf possYour nounCode.

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)?

Re: Use What Works: Prefixing Database Tables With 'tbl'

#19

Post by MSM » Wed, Oct 10, 2012, 3:24 PM UTC

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.

Re: Use What Works: Prefixing Database Tables With 'tbl'

#20

Post by pradocchia » Wed, Oct 10, 2012, 3:24 PM UTC

naelyn wrote: post #17

bunderbunder wrote: post #15

Earlier quoted context omitted.

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