Software Documentation.Info Logo

Subscribe to our blog and be notified about new business software resources
  home | technical writing | business software | software documentation tools | service business software | software testing | contact

Other Software Links

Home > Software Documentation Articles > Documenting your MySQL database

Documenting your MySQL database

This page describes how to document the databases in your MySQL relational database servers. The article includes database naming conventions, adding object descriptions, documenting stored procedures, and how to create technical documentation for your MySQL Server databases.

Much of this article can also apply equally to other relational databases such as SQL Server, MySQL, PostgreSQL and Microsoft Access.

MySQL Database Naming Conventions

If you're designing a new database, then it makes sense to follow a logical database entity naming convention. The following are a few suggestions for naming the objects in your new database.

General naming conventions

Database names should where possible use alphanumeric characters only. Avoid using hyphens as they can subsequently make it difficult to compose certain MySQL SQL database queries.

If possible, also try to avoid using spaces in database entities, particularly in table names and column names. While MySQL 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 SQL, such as month or year.

Table naming conventions

Tables should where possible be given names that relate to the data stored within them. For example, employee data should be stored in a table called Employees. 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. EmployeeData_ can help to group tables into related categories. Some organisations have their own particular naming scheme for this.

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, InsertUserDateOfBirth or UpdateCardPaymentInfo.

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.

Documenting MySQL Stored Procedures

Remember also to document the 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: Geoff Titman
Create Date: 15/09/2010
Param: @UserName = User's login name
Param: @Password = User's password
Return: UserID of the user
Modified Date: 10/10/2010
Modification: Added to check to see if the user's account has been suspended

Note that adding comments to stored procedures has no affect on their performance.

Creating Database Technical Documentation

Asides from creating database diagrams, there isn't much functionality in MySQL that allows you to create documentation for your database.

Fortunately there are timesaving 3rd party MySQL database documentor tools that will automatically create comprehensive technical documentation for MySQL databases.

For software applications that use MySQL databases, there are integrated documentation generators such as the PHP Documentation Tool, which is a code documentor for PHP web applications and associated MySQL or SQL Server databases.

MySQL Documentation Tool
Database documentor tool for MySQL databases

Related content: mysql, documenting, documentation, how to, guide

© 2007 - 2012 Winnersh Triangle Web Solutions Limited.
Registered company number: 4493816.

Page created Thursday, July 01, 2010. Last modified Thursday, July 01, 2010.
About This Website | | Site Map | Privacy Policy | Add Your Site