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.

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.