Use What Works: Prefixing Database Tables With 'tbl'
thomaslarock.com
Use What Works: Prefixing Database Tables With 'tbl'
1–10 of 47 posts
Re: Use What Works: Prefixing Database Tables With 'tbl'
#2adjHungarian nounNotation verbHurts detThe nounReadability prepOf possYour nounCode.
Re: Use What Works: Prefixing Database Tables With 'tbl'
#3Wow. 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. :)
Re: Use What Works: Prefixing Database Tables With 'tbl'
#4Wow. 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.
Re: Use What Works: Prefixing Database Tables With 'tbl'
#5adjHungarian 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.
Re: Use What Works: Prefixing Database Tables With 'tbl'
#6There 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.
Re: Use What Works: Prefixing Database Tables With 'tbl'
#7adjHungarian 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.
Re: Use What Works: Prefixing Database Tables With 'tbl'
#8> 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.
Re: Use What Works: Prefixing Database Tables With 'tbl'
#9I 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.
Re: Use What Works: Prefixing Database Tables With 'tbl'
#10Wow. 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.