Browse DevX
Sign up for e-mail newsletters from DevX


Develop a Consistent Naming Convention for Your Database Objects : Page 3

The sheer number of objects in a database makes keeping track of them difficult. As your system grows in complexity, identifying the objects' dependencies and their purposes can quickly become confusing. Adopt a naming convention that limits the ambiguity and uncertainty surrounding an object and its purpose.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

View Naming
Views typically contain joins, so they are hybrids, not base tables that are talking about a single entity. Views can be used to partition tables either into security or access levels, or by subject instead of application. In my experience, they most commonly represent a security mechanism to hide the details in a table from users who don't need to see all the data in a table. Most commonly, views represent joins between entities.

I prefer to name views with prefixes to distinguish them from tables. You can use views together with base tables in select statements, and I find it convenient to segregate them, especially since views are much more likely to already contain a join. MS adopted the ANSI idea of using Information_Schema, a prefix for the views that deliver the old sysobjects columns. These are views that deliver the content one queries to find out metadata about his or her database. There are Information_Schema.tables (as in select * from information_schema.tables, information_schema.columns, or information_schema.table_constraints). Using these views is much more convenient than having to remember how to navigate the intricacies of the system tables. Using the information schema views simplifies your life because their naming convention communicates their purpose right out front (and yes, they are implementing joins for you).

Suppose you were responsible for a database that supplies brands offering product promotions to a Web application. Further, the database has to keep customer information with each brand. You would structure an account table with a hierarchy in the accounts with brand at the highest level. Fielding a brand with its customers would always wind up being a two-step operation because you would have to collect all the accounts that belonged to a particular brand. In other words, you would be constantly creating a master-detail select statement. You would use the brand id to field the order-taking process on the Web for the same catalog of products offered under different names and prices for each particular brand.

This would be an ideal spot to create views that are specific to a particular brand. You would wind up with views for all the brands you had, say a Sony brand, a Motorola brand, an IBM brand, and an HPCompaq brand. The underlying table would have some redundancy, because if "Joe" bought something from one brand and then bought something else from another brand, he would be an account in two brands. Using views to isolate these associations would create a shortcut for segregating those multiple appearances so that Joe would appear once in any view. I would name the views vw_BrandSony, vw_BrandIBM, etc.

More prosaically, views are also extremely useful for partitioning tables according to privileges and/or authorities granted to their users. This makes it a good security enforcement vehicle for segregating data that you don't want seen, because you can deny permissions to the table and allow only those columns and rows that are appropriate to the login asking for data. They cannot change what they cannot see.

Stored Procedure Naming
Another place where a naming convention can be useful is in the naming of stored procedures. We all know (or should know) that naming your stored procedures the same way as Microsoft qualifies as a worst practice. When you do that, you force the server to look at the Master database first. The sp_ prefix signals that the code to run is in the Master database. If the procedure is not found there, then the server seeks it in the user's database. This just wastes time sending the processor out of the intended database, looking for a procedure that is not where its name says it should be, and forcing it back to the user's database. (Procedures that include their owner names are preferred, but that is a whole article unto itself.) The moral of the story is that you should name your own stored procedures something other than sp_ , like usp_, to avoid sending the procedure call out to Master.

Once you have settled on a different prefix, let's say usp_, a natural tendency is to name something like usp_InsertCustomer, usp_DeleteCustomer, usp_UpdateCustomer, and usp_SelectCustomer - natural, but not wise! The tree view for procedures becomes ordered by the first few characters of your stored procedure name, causing all the stored procedures to be sorted by function instead of by object. I bet that isn't what you want.

Try placing their table names immediately after the underscores (some people hate underscores, but I like them in proc names). That way, they will naturally sort in object name order, so all your customer procedures will fall together, all your order procedures will fall together, and if you have a library of utility or other special-purpose stored procedures, then they will fall together in the library, API, or utility section of the tree view.

Naming Triggers
For triggers, I would reverse the object/function convention in the previous section because of the dedicated nature of their ownership. Triggers are by definition bound to their tables. This gives you have some flexibility in naming them because the owning table circumscribes its ownership. Triggers have other complexities though in that they also are named for specific CRUD functions (well, not the Read part of CRUD, but they do exist for Update, Create (insert) and Delete). These should appear as part of the trigger name to make its purpose explicit. If the trigger is multi-purpose, that is, its action can apply to more than a single database action, then all its parts should be reflected in the trigger name to keep ambiguity at a minimum (e.g., tr_InsUpd_CustomerBalance).

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date