PracticalWeb Ltd

Websites that work for you.

Adding Comments to the Database

Comments in code are widely used and generally (but not universally) accepted as a good thing.

Databases can also accommodate comments - but these seem to be very rarely used.

I’m of the opinion that database comments are very useful.

It’s often hard to determine the exact meaning of a column - maybe it would be ideal to rename the column but you don’t want to risk breaking code.

I’ve also worked on a few projects where tables retain redundant columns just in case a query somewhere selects this column. Then every now and again a developer (like me) gets tripped up and thinks this column is meaningful.

For a really simple table Create Table: CREATE TABLE phonebook ( number int(11) NOT NULL COMMENT ‘phone number’, name varchar(255) default NULL, PRIMARY KEY (number) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

To add a comment to a table alter table phonebook comment=‘an example table’

To add a comment to a column repeat the existing column definition and add the comment at the end alter table phonebook change column name name varchar(255) default NULL comment ‘full name’

The comments are then displayed when you show create table

It’s even easier to put the comments in at the start.

mysql> create table phonebook (number int(11) primary key comment ‘phone number’, name varchar(255) comment ‘full name’) comment ‘an example table’; Query OK, 0 rows affected (0.05 sec)

show create table phonebook\G 1. row Table: phonebook Create Table: CREATE TABLE phonebook ( number int(11) NOT NULL COMMENT ‘phone number’, name varchar(255) default NULL COMMENT ‘full name’, PRIMARY KEY (number) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=‘an example table’ 1 row in set (0.00 sec)

The above is an example based on MySQL.

Comments