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.