Should I comment Tables or Columns in my database?

Question

I like to comment my code with various information, and I think most people nowadays do so while writing some code.

But when it comes to database tables or columns, I have never seen anyone setting some comments, and, to be honest, I don't even think of looking for comments there.

So I am wondering if some people are commenting their DB strcuture here, and if I should bother commenting, for instance when I create a new column to an existing table?

Answer 1

Yes, it is quite unusual. This is mostly down to:

• People don't know it's possible- progressively people deal less with RDBMSs directly and know less and less features, preferring to do everything on the main (non-SQL) codebase. ORM users, for instance, comment their models
• Tools rarely show the comments easily, they tend to be buried and thus people don't see them
• Well-designed databases tend to be self-explanatory for someone who knows the "business". Proper table and column naming mean that most tables and columns might not need comments- redundant comments are bad; "this table contains students" as a comment on the "students" table is worse than useless. Constraints can also make some comments redundant (i.e. "this column must be null if that column equals 3" can be implemented using a CHECK constraint- and constraints can be easily listed).

I believe database documentation is useful; I like autogenerated documentation such as that generated by tools like SchemaSpy; these often make comments pretty visible. However, my experience is that documentation is mostly useful when learning the database- a few months in any project and you'll know the schema by heart, unless it is extremely complex, thus docs are not that useful for reference.

Answer 2

Totally.

Microsoft SQL Server supports comments on both tables and columns, and it's pretty easy to set them through the IDE (Microsoft SQL Management Studio). Many other databases support comments as well.

Commenting tables and columns is as, if not more critical as commenting code. Having external documentation is great, but it's easy to forget to update the external documentation at every change. You can also forget to update the comment of a column, but it's more complicated (and it's also much easier to update the comment of a column rather than searching for the place to update in the external documentation).

You may wonder why you don't see too much comments. For the same reason that:

• Most databases are completely undocumented. No comments. No external documentation. Nothing.

• Most databases lack constraints, so you never know what are the values which can be saved, and what values will make the application relying on the database crash.

• Most databases don't have correct indexes, primary keys, etc.

• Most databases don't follow any naming convention.

• Most companies don't have DBAs, don't have money and don't understand that having a high quality, well maintained database is critical for them.

Answer 3

Just to add the all the Above good answers..

What I do is Maintain my Descriptions\Comments in the BD, But because no one looks there (or may not have access) I use those comments to auto-generate a Data dictionary, that I can then keep with the rest of my Docs!

Answer 4

Commenting database columns is part of the modeling task. There are many steps to prepare a data model from concept to production. Describing each column is part of that process and almost all first class ERD tools will carry forward the comments from the modeling environment to the physical database environment. There is a difference between column description and column comment. Comments are usually short where descriptions are comprehensive and long. If you have a description, you'd rarely need comments. It is arguable as what should you put in the description. Part of your project standards should cover this in the meta data policy.