Thursday, February 10, 2011

View Naming Conventions

Naming conventions sometimes instigate conflict within the world of DB2, especially when it comes to views. But, really, it should be very easy. Just always remember, that a view is a logical table. It consists of rows and columns, exactly the same as a DB2 table. A DB2 view can (syntactically) be used in SQL SELECT, UPDATE, DELETE, and INSERT statements in the same way that a DB2 table can. Furthermore, a view can be used functionally the same as a DB2 table (with certain limitations on updating as outlined in my article).

Therefore, shouldn't it stand to reason that views should be held to the same naming conventions as are used for tables? (As an aside, the same can be said for DB2 aliases and synonyms).

End users querying views don't need to know whether they are accessing a view or a table. That is the whole purpose of views. Why then, should we enforce an arbitrary naming standard, such as putting a V in the first or last position of a view name, on views?

DBAs and technical analysts, those individuals who have a need to differentiate between tables and views, can utilize the DB2 Catalog to determine which objects are views and which objects are tables.

Most users don't care whether they are using a table, view, synonym, or alias. They simply want to access the data. And, in a relational database, tables, views, synonyms, and aliases all logically appear to be identical to the end user: collections of rows and columns.

There are certain operations that cannot be performed on certain types of views, but the end users who need to know this will generally be sophisticated users. For example, very few shops allow end users to update any table they want using a report writer or query tool (e.g. QMF, SPUFI, etc.). Updates, deletions, and insertions (the operations which are not available to some views) are generally coded into application programs and executed in batch or via online transactions. Most end users need to query tables dynamically.

Now you tell me, which name will your typical end user remember more readily when he needs to access his marketing contacts: MKT_CONTACT or VMKTCT01?


Anonymous said...

Craig, the problem is that the table is not MKT_CONTACT but TB_MKT_CONTACT.
And the view has to be named differently, right ?
Thanks, Ulf Cihak

Craig S. Mullins said...

Well, I'd take issue with the TB in front of the table name. It is not needed, and it shouldn't be part of the table naming standard. If the table naming standard is basically to be descriptive of the data, then there should be enough difference for a view based on that table to name the view differently. If there isn't, then why is the view even needed? So maybe we have a table named MKT_CONTACT. And we need a view with the contact info joined to the company table for the addresses. Then we'd have a view named MKT_CONTACT_W_ADDR (or something like that). But the table names have no "T" or "TB" in them and the view names have no "V" in them...

Karen Lopez said...

I have to agree with Craig here. In the old days we had to stuff meta data into table and column names because we didn't have advanced tools that could help us work with database objects like we do now.

Some people want to hang a lot of meta data in their object names. Instead, we should use tools to help us understand objects.

If you have to put TBL or T_ in front of an object name just so that you know its a table you probably shouldn't have database access at all.