Adding comments, or remarks, to database schema objects.

Last week I was asked a question about the ability to provide explanatory remarks to database schema objects. The question focused on the typical remarks that elucidate the intent or purpose of the schema objects; however, keep in mind that remarks can also specify justification of standards or best practice violations, stored procedure limitations, use of undocumented features, column data units, conversion information, and so forth. An experienced database designer, or database developer, will make every attempt to create self-documenting schema objects. In the event that there is information that cannot be expressed by the name of the schema object itself, the database designer/developer will apply an appropriate schema object remark. The remarks are stored in the system catalog. In addition to providing an essential inline database reference for the developers and/or maintenance team, remarks make you think about the design.

While this capability is not part of the SQL standard, it is supported by the major DBMS vendors. Every major vendor except Microsoft supports adding explanatory remarks to their respective system catalogs through the use of the COMMENT statement. DB2 provides support for up to 254 characters while Oracle supports up to 2K. DB2 stores the remarks inline with the other metadata in the system catalog. For example, to access remarks in the DB2 system catalog you would supply the following SQL:

SELECT tabname, remarks FROM syscat.tables where tabschema=’DEV’

Both Oracle and Microsoft do not store the remarks inline with the other metadata in the system catalog. You access Oracle remarks using the system views USER_TAB_COMMENTS and USER_COL_COMMENTS. In Microsoft SQL Server, you access remarks through the sys.extended_properties catalog view. Microsoft SQL Server does not limit you to applying only one piece of explanatory information per schema object; however, the stored procedures to create and manipulate schema object remarks are laborious when compared to the other vendors.

Do yourself a favor and spend some time reviewing the schema object documentation capabilities of your DBMS. Remember the remarks that you supply to the system catalog are available to the team when they are working with the physical database. The team will love you for it, especially when you are not around.

Posted at 08:18 PM  Permalink