| This article describes how to document your Microsoft SQL Server
database. The principles are similar for all versions of SQL Server, although SQL Server
2005 and above contain a number of neat enhancements to database documentation
capabilities. The article includes database naming
conventions, adding object descriptions, documenting stored procedures, and how to create technical documentation for your SQL Server
databases.
Much of this article can also apply equally to other relational databases such as
Oracle, MySQL, Microsoft Access and PostgreSQL.
Database Naming Conventions
If you're designing a database from scratch, then it makes sense to follow a logical
database entity naming convention. The following are some suggestions for when naming the
objects in your new database.
General naming conventions
Database names should if possible use alphanumeric characters only. Avoid using hyphens
as they can subsequently make it difficult to write certain Transact SQL queries!
Where possible, try to avoid using spaces in database entities, particularly in table
names and column names. While Transact SQL can still refer to these entities if they are
enclosed in square brackets, it can sometimes lead to confusion and coding errors.
It is also a good idea to avoid using table and column names that are reserved words in
Transact SQL, such as month, year or user.
Table naming conventions
Tables should be given names that relate to the data stored within them. For example,
employee data should be stored in a table called Employees. Note that the plural
form of the word is used, as there will more than likely be more than one employee stored
in the table.
Some developers prefix table names with something like t_. Such prefixes can be
particularly useful if the tables are referenced from application source code, as it makes
it more obvious to the software development team that a table rather than a view or some
other entity is being referenced.
Giving a table a prefix related to its function (e.g. Payroll_ can help to group
tables into related categories. SQL Server 2005 introduces the concept of schemas.
This allows tables to be grouped accordingly. For example, the AdventureWorks sample
database contains a HumanResources schema, and the associated tables (Employee,
EmployeeAddress, EmployeeDepartmentHistory etc.) are all listed under this schema in the
SQL Server Management Studio table list.
Foreign key naming conventions
It is particularly useful to be able to identify the foreign keys within a database
table. Prefixing them with something like fk_ makes it much more straightforward to
identify table relationships just by looking at the table's columns.
Stored Procedure naming conventions
It is always useful to name stored procedures according to their use. For example:
GetUserID, InsertDateOfBirth or UpdatePaymentInfo.
On occasions, it is useful to add a suffix to show the stored procedure's input
parameters. For example the stored procedure GetUserIDByUserNameAndPassword will return
take a user name and password as input parameters and return a user ID . This can be used
to differentiate stored procedures that have similar functionality but have different
parameters. This would allow other related stored procedures to be added, e.g.
GetUserIDByGUID and GetUserIDByApplicantID. The downside to this naming convention is that
the stored procedure names can become quite long.
Some developers prefix all their stored procedures with certain tags. A popular
convention is to name a stored procedure with a sp_ prefix. However, this is not
recommended best practice for two reasons. Firstly, there is a slight reduction in
database performance, as the SQL Server will check for stored procedures with this prefix
in the master database first. This performance reduction is small, but it may be
significant in high end enterprise systems. Secondly, since Microsoft uses this prefix for
system stored procedures, there is always the chance that you could give your own stored
procedure the same name as a system stored procedure. It is also possible that a future
version of SQL Server could introduce a new system stored procedure with the same name as
one of your existing user stored procedures.
If you do want to prefix your stored procedures then it is recommended to use something
like usp_ or sproc_. Likewise a function could be prefixed with something
like func_. Such prefixes can be particularly useful if the stored procedures are
called from application source code, as it makes it more obvious to the software
development team that a stored procedure is being called.
Documenting SQL Stored Procedures
Don't forget to document the Transact SQL code of stored procedures and functions.
While simple queries should be self explanatory, larger queries will benefit from
documentation. Stored procedures will also benefit from a standard header, which at the
very least should describe the procedure's functionality. Including a change log will also
help to track changes, particularly if you don't have any source control system in place.
An example header for a stored procedure is shown below:
/*
Description: Gets a user's UserID
Author: Brett Burridge
Create Date: 14/09/2007
Param: @UserName = User's login name
Param: @Password = User's password
Return: UserID of the user
Modified Date: 10/10/2007
Modification: Added to check to see if their account has been suspended
*/
Note that adding comments to stored procedures has no affect on their performance.
Database Object Descriptions (MS_Description)
Being able to give database objects descriptions goes some way towards being able to
create a self-documenting database.
SQL Server 7.0 introduced the useful ability to add a Description to a table
through the table design window. SQL Server 2000 enhanced this functionality by
introducing extended properties.
Unfortunately the SQL Server 2000 Enterprise Manager has limited capabilities for
allowing these properties to be edited. However, in SQL Server 2005 the SQL Server
Management Studio GUI allows extended properties to be edited. Most objects in a database
(e.g. tables, table columns, views, functions, stored procedures, the database itself)
have extended properties that may be edited. By default there is only a single extended
property, MS_Description. Even more limiting is that although you can use the
MS_Description extended property to add descriptions to objects, without 3rd party add-ons
such as our SQL Documention Tool, there
isn't actually a lot you can do with them once entered.
Creating Database Technical Documentation
Asides from creating database diagrams, there isn't a lot of functionality within SQL
Server that allows you to create technical documentation for your database.
Fortunately there are timesaving 3rd party SQL Server documentor tools that will
automatically create comprehensive technical documentation for SQL Server databases. Our SQL Documentation Tool was launched in 2005,
and is a low cost option for quickly creating technical documentation for SQL Server
databases with the minimum of effort.
For applications that use SQL Server databases, there are utilities that will create
technical documentation for that application plus its associated SQL Server databases.
Examples of this are our ASP Documentation
Tool (for ASP VBScript and JScript and associated databases), .NET Documentation Tool (for .NET
Framework C# and VB.NET code and associated databases), VB Documentation Tool (software documentation
tool for Visual Basic 6.0 applications and their associated databases), and PHP Documentation Tool (code documentor for
PHP web applications and associated databases).
Further Reading
|
