 |
ASP Kitchen |
|
ASP Kitchen |
|
ASP Kitchen: SQL Server Articles: Documenting your SQL Server database Documenting your SQL Server databaseThis article describes how to properly document your Microsoft SQL Server databases. The principles are similar for all versions of SQL Server, although SQL Server 2005 and above contain a number of great 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 ConventionsIf 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 conventionsDatabase 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 conventionsTables 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 conventionsIt 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 conventionsIt 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 ProceduresDon'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:
/* Note that adding comments to stored procedures has no affect on their performance. Database Object DescriptionsBeing 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 the SQL Documention Tool utility, there isn't actually a lot you can do with them once entered. Automatically Creating Database Technical DocumentationAside from creating database diagrams, there isn't a significant amount 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. My 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 the 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 6.0 Documentation Tool (software documentation tool for Visual Basic 6.0 applications and their associated databases), and PHP Documentation Tool (code documenter for PHP web applications and associated databases). Useful Development Tools
Author detailsBrett Burridge has worked as a web developer since 1997 and has developed web applications for a range of corporations, start up busiensses and educational establishments. Brett is presently employed as an Internet developer and technical writer through his own company, Winnersh Triangle Web Solutions Limited. The company produces a number of innovative products, including a range of software documentation tools, which include the ASP Documentation Tool™, the .NET Documentation Tool for VB.NET and C#, and the SQL Server Documentation Tool. Other products include The Website Utility, which functions as a website error checker, search engine optimizer and ASP/ASP.NET search engine builder application. As well as the ASPAlliance, Brett has written articles for Ariadne.ac.uk, ASPToday, the software documentation portal www.softwaredocumentation.info, and has contributed recipes to the ASP.NET Developer's Cookbook. links Outside web development, Brett is interested in travelling (here are my travel logs from New York, Hong Kong and Tokyo), digital photography (here's my photo gallery), tropical fishkeeping and collecting contemporary works of art by artists such as Doug Hyde. Contact Brett by emailing
ASP Kitchen: SQL Server Articles: Documenting your SQL Server database |
|
||||||||||||||||||||||||
| © page content copyright Brett Burridge 1998 - 2008. | |||||||||||||||||||||||||
