How to comment your MySQL database

Technically, I’m not a database administrator. Practically, I write SQL statements on a regular basis. I also create new tables and add columns to existing tables with some frequency. And I’ve noticed that most people don’t “comment” their databases. This is probably fine for scratch projects that never make it to production and are only ever maintained by one person. However, if there’s the slightest chance that someone else might be maintaining the database that you created, it’s a good idea to comment your database, just as a developer will comment their code.

Here’s an example of how you do it in MySQL:

CREATE TABLE `prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'table index',
  `ticker` varchar(16) NOT NULL COMMENT 'stock ticker symbol, e.g. GOOG',
  `day` date NOT NULL COMMENT 'date of price info for this stock ticker',
  `open` DECIMAL(20, 8) DEFAULT NULL COMMENT 'opening price for this stock',
  `close` DECIMAL(20, 8) DEFAULT NULL COMMENT 'closing price for this stock',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='record of opening and closing prices for stock' AUTO_INCREMENT = 1000;

If you’re adding a column to a table, you can comment it at the same time, like this:

ALTER TABLE prices ADD COLUMN `volume` int(11) DEFAULT 0 COMMENT 'trading volume for this stock' AFTER `close`;

In my example, the columns and their names might seem obvious to people who are remotely savvy with finance. But just because you know something about finance, doesn’t mean the next dev will have this somewhat specialized knowledge. Your comment can help them find the information they need to understand what the column means and how to use it. Furthermore, in many business applications, database schemas may consist mostly of fields which only have meaning within the business itself. Comments on those fields can be a very handy source of documentation when the original developers are long gone.

As an aside, my prices table is only an example table for holding stock prices. You can see I really went overboard in allowing 8 decimal places for a stock price. Let’s just say I “future-proofed” it. Here’s a sample INSERT statement:

INSERT INTO prices (ticker, day, open, close) values ('GOOG', '2059-12-21', 401200022445.09787456, 401200023899.09787456);
SELECT * FROM prices;
+------+--------+------------+-----------------------+-----------------------+
| id   | ticker | day        | open                  | close                 |
+------+--------+------------+-----------------------+-----------------------+
| 1000 | GOOG   | 2059-12-21 | 401200022445.09787456 | 401200023899.09787456 |
+------+--------+------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

And here’s the CREATE TABLE statement as viewed by a developer trying to figure things out:

SHOW CREATE TABLE prices;
...
| prices | CREATE TABLE `prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'table index',
  `ticker` varchar(16) NOT NULL COMMENT 'stock ticker symbol, e.g. GOOG',
  `day` date NOT NULL COMMENT 'date of price info for this stock ticker',
  `open` decimal(20,8) DEFAULT NULL COMMENT 'opening price for this stock',
  `close` decimal(20,8) DEFAULT NULL COMMENT 'closing price for this stock',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COMMENT='record of opening and closing prices for stock' |
1 row in set (0.00 sec)

If you found this interesting, click the subscribe button below! I write a new post about once a week.

Leave a Reply

Your email address will not be published. Required fields are marked *