Naming Conventions for Tables and Views

In years past I taught the Microsoft 2779B Implementing a Microsoft SQL Server2005 Database class, and in Module 7 (focusing on Views) I came across the following “tip”:

Tip: You should develop a consistent naming convention to distinguish views from tables. For example, you could add the letter v or the word view as a suffix to the name of each view that you create. This approach allows tables and views to be easily distinguished.

Now, I agree that you should develop a consistent naming convention, but I advised the students that (in my opinion) views are, from the user’s perspective, no different than tables. The relational model makes little distinction between tables (relational variables, or “base” relvars) and views (“derived” or “virtual” relvars).

In fact, in C.J. Date’s book An Introduction to Database Systems(8th Edition, Addison-Wesley/Pearson Education, 2004, pg. 75), Chris states “Indeed, the fact that a view is a relvar is precisely one of the strengths of relational systems; it is important in just the same way as the fact that a subset is a set is important in mathematics. Note: SQL Products and the SQL Standard often seem to miss this point, however, inasmuch as they refer repeatedly to ‘tables and views’ with the tacit implication that a view is not a table.”

In other words, from the perpective of an application or from the user, there is no difference between a view and a table. Now, we know that’s not completely true, because it’s easy to create a view that can’t be updated because it joins multiple tables, and the query processor can’t parse out what’s being updated amongst the various tables in the view. That’s a relatively easy problem to solve, however, with the INSTEAD OF trigger.

In a former company we had an application that manages web sites. It uses a master database for all sites, and then a database for each site. In each database is a table that the application uses to display exhibitors at our trade shows. Some of the trade shows have multiple shows around the country, and for these the site database is actually a set of databases with a parent database for the main site and child databases for the individual shows in the group. In the child databases the table is replaced by a view, presenting data in the parent database. The application had a problem with an update to this object, so I wrote the INSTEAD OF trigger, and the application works as it was supposed to. I didn’t have to create views in every database used by the application for this exception situation.

So, my point is that tables and views should follow the same naming standard – the difference between the two should be transparent to the user.

 

About the Author

Microsoft SQL Server MVP and Practice Leader

Allen White

Allen White is a Microsoft SQL Server MVP and Practice Leader at UpSearch.

For over 30 years, Allen has specialized in developing applications that manage the movement of data and maximize data’s usefulness. Allen excels at communicating highly technical information using language that results in increased client engagement and understanding, regardless of technical competency.

Allen has been working with relational database systems for almost 20 years. He has architected database solutions in application areas like retail point-of-sale (POS), POS audit, loss prevention, logistics, school district information management, purchasing and asset inventory and runtime analytics. Allen thrives on providing comprehensive solutions to information management problems across a great variety of application environments.

Learn more about Allen White at https://upsearch.com/allen-white.

 

About UpSearch

up-social-round UpSearch provides as needed Microsoft SQL Server DBA Services across the globe. We specialize in helping leaders protect, unlock and optimize data’s value.

 

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *