Naming Conventions

The other day I read Aaron Bertrand's blog post about his stored procedure development best practices which got me thinking about the standards I have developed over the years so I decided to do a short series on my personal standards.

Let's start with naming conventions.

1. Object names in all lower case with an underscore, "_", between words. With this standard I am not affected by the case-sensitivity of the collation.
   
2. Table names are plural because they represent sets of an entity, companies not company.
   
3. Common column names like id, name, or description should be prefaced with the singular of the table name so our companies table would have company_id, company_name as columns. This is so that they do not need to be aliased in queries when there are multiple columns with the same name. For example, if I have a query that joins products, orders, and companies, I would do P.product_name, C.company_name instead of P.name as product_name, C.name as customer_name.
4. Use clear and descriptive names for objects. For example, companies instead of cos or last_name instead of lname.
5. Settle on common abbreviations. I like "desc" for description in columns like product_desc. I also prefer "no" to "num" for number. I'm not really stuck on either, but I believe you need to set the standard and be consistent.
6. For date columns decide on either date_column or column_date. I prefer to use birth_date or start_date over date_birth or date_start. Again, I can live with either, just pick one.
7. I like to prefix my views with "vw_". I understand the arguments against this practice, but when I am in code I want to know when I am referencing a view and not a table, since thay can be used interchangeably and can change performance.
8. Stored procedures are entity_action. Where action is ins, upd, del, get, list, find. I don't see the need for any prefix as the use tells you it is a stored procedure.
9. Function names are fn_entity_action to distinguish them from stored procedures.
10. Cursors, in rare use, are c_descriptive name.
11. CTE's in 2005 and later are cte_descriptive name
12. Triggers are trg_table_reason_on_action(s). For eaxmple for auditing purposes I would have a trigger named trg_persons_audit_ins_upd_del. I have typically only used AFTER triggers so I have not developed a standard for defining the type type of trigger. I would probably start with something like this for an INSTEAD OF trigger, trg_persons_io_ins_upd_del. I'm not sure I like that one so I'll take suggestions.
    

Next up will be a post on stored procedure and trigger formating.