What is the purpose of tbl_Product, tbl_Order naming conventions?

Question

I've seen tbl_ prefixes decorating all tables in the last two code bases I've worked with. I can't think of any reason they'd be useful for application developers or database administrators.

If a DBA needs to see what objects are tables they can always join up to DMV's or the schema tables in master right? I can't think of how they'd be useful to a programmer either, even more so if the project is using an ORM tool.

Still even while writing stored procs they just seem to get in the way.

Can anybody explain how they'd be useful in a non-subjective way? Ex ( having tbl_ helps me perform x task )

Answer 1

I can't think of many benefits. You could definitely see cleaner SQL like this:

select post.id, post.subject, post.body from tbl_post post where post.author="eric"

Makes your variables easier. Otherwise, this just looks like you've been dealing with people who learned databases on MS Access.

Answer 2

I’ve heard the same thing over and over again the reason being, it helps them to know the type of the object.

Inside a query, using prefixes could help them separate tables from views for instance.

I really don’t agree with that. After getting used to the database schema, prefixes become redundant and as happens with everything that is redundant, it can become desynchronized or make changes harder to do.

Let’s say you have a table that for whatever reason you have to split into two tables. Let’s say you decide to create a view that emulates the original table selecting data from the two new tables.

Are you going to rename throughout you code base or are you going to stick with a view prefixed as tbl_?

So my point is database object names should not have any prefixes for inferring their types.

Answer 3

This is hungarian notation, or rather a misuse of it. The original use was to put some important aspect of the use of a variable as a prefix. The fact that a table is a table is hardly a useful way to use hungarian notation.

In ASP hungarian notation is used to specify a data type, as VBSCript only has variants. I've seen ASP programmers apply this to tables and fields in the data base too, that is one way that this misuse come into use.

Answer 4

the one benefit of it is that you can tell the difference between view, table, and materialized view. Of course, this doesn't really matter when you are writing the code, it is maintaining it that matters. If someone realizes that they are pulling from a view, they might be able to optimize the code better. Views based on views based on views can be very inefficient. Without the tbl_ or view_ prefix, it might be more difficult to tell if this is happening.

Answer 5

I think that when using an ORM such as Entiry Framework, it may make it easier to tell which side the the mapping is dealing with tables and which side is dealing with objects (entities). My tbl_Employee maps to Employee, for example. There is no way to get the layers of abstaction confused.