DocumentationAs you come up with company or enterprise standards, it is of utmost importance to document them. After all, what good are standards if no one knows about them? Make sure your documents are posted in a Lotus Notes database or on the Intranet somewhere.
Table NamesThe most important thing to keep in mind when it comes to naming any SQL Server object is that your business needs may change down the road. You may be thinking your application has a relatively small backend but your next release may include some type of auditing, which makes your database grow 100 fold. It is this type of unpredictability that I generally chose the lowest common denominator. For example, Oracle and DB2 both have restrictions that will make your portability to them quite expensive if you don't take the lowest common denominator of both of the database systems. It would be nice if your application could always be on SQL Server, but as politics move in your company, so may the database. I'm sure many of you have cringed at the site of a database vendor taking your boss out to a golf game.
With that in mind, there are two compatibility issues with DB2 and Oracle that must be addressed. The current version of DB2 only allows for 18 characters. In a future release slated for this winter, it will support 30 characters. I chose to play it safe with 18 characters knowing how slow most companies move. Oracle supports only upper case table and element names easily. If Oracle is even on your radar, make sure your SQL Server table names and field names are always in upper case. If you issue a create table statement in Oracle, Oracle will convert it all to upper case however, some of the migration utilities do not give you this luxury.
If you have application components such as a accounting system that you know you're going to integrate with other applications, you will need to prefix those components for easy migration. For example, if you have a workflow system that you use in 8 different applications, prefix those tables with the two letter abbreviation WF (WF_QUEUE). If you do this, you can easily find the tables in Enterprise Manager. At the table level, avoid using abbreviations in other cases.
Column NamesField names again should be all upper case. They should be descriptive to their purpose and only use abbreviations for a select few names. I have a list of 24 abbreviations that I use on a regular basis (NAME=NM, DESCRIPTION=DE). Anything else, I use the entire word. Use underscores to separate words.
Stored ProceduresThe same rules apply here for length of fields. Also if your developing a component to an application versus the entire application, prefix the stored procedure name with a 2 letter prefix. I begin my stored procedures with SP__ (2 underscores) and then the component prefix. You can reduce the learning curve tremendously if you use descriptive names and if your name denotes whether your stored procedure selects, inserts, updates or deletes data.
- SP__WFSELECTBORROWER (WF is the workflow system)
- SP_APINSERTBILLRECORD (AP is the Accounts Payable system)