Database schema documenting with DTM Schema Reporter

Developers of solutions based on DBMS, end users and database administrators (DBAs) very often come across situations when it is necessary to get the more or less detailed description of the database schema (metadata) being used. Let us take several typical tasks that can be solved:.

• Schema audit. After an information system has been developed for a long time, the database schema may get filled with changes and extensions not documented in the original database design (database model). It is much more convenient to study the database schema state or to discuss it in a team of developers if you have well-formatted documents (including a hard copy) where items come with comments.
• Providing the schema to your customer or a third party. In most cases, information for presentation should look nice and have the necessary explanations. It is inexpedient to use a low-level schema presentation for that.
• A database structure report about what has been done while launching the system. This case differs from the second only in the fact that maximum detailed information is required here. Usually, you need not only a report on the schema itself, but also predefined users descriptions, their access rights to database objects.
• Security audit. Detailed information about accounts, logins and rights to objects created in the database will help a DBA or a security administrator evaluate the situation, detect drawbacks and manage database access more effectively.

An important issue while documenting a database schema is extracting comments to tables, fields and other database objects. Some DBMS have special mechanisms for that on the level of the system kernel and support SQL statements like ADD COMMENT. In this case, it makes sense to extract already existing comments and include them in the report while documenting the schema. DTM Schema Reporter works according to this principle supporting comments to database objects for the most popular database systems.

However, there are a lot of systems (especially desktop-based) that do not support this mechanism. Also, authors could have failed to implement creating notes during database design and development. For this case, DTM Schema Reporter provides you with the manual input of required annotations at the stage of report preparation. This class of the database utilities also known as SQL documentor or database documentor.

Another aspect of documenting the database schema is selecting the report format. The HTML format is a good choice for publishing the schema definition on the corporate Intranet or Internet site. DTM Schema Reporter supports three choices for this format:

• A single file with the table of contents containing internal links.
• A frameset. In this case, the table of contents is located in a separate frame and is always visible.
• One file per. In this case, several HTML documents are created and the table of contents is also located in a separated file.

The RTF format (compatible with Microsoft Word) is the most popular format in case if you want to change the document formatting or include the schema definition in another document. Moreover, this format is supported on a number of non-Windows platforms, which extends the area of its application.

The XML format is the most comfortable one if you are going to process the database schema definition later in another program that cannot read the schema directly. Also, a few XML viewers allow you to view the document in a tree-view format, which may improve the visual representation of information for great schemas.

The Windows HTML help format (CHM) has convenient tools for navigating and searching through the generated report and also seems to be the most compact format out of those mentioned here.

In case you need to make the created report maximum platform-independent, you should use the PDF format also providing comfortable tools for getting a hard copy of the report.

The Text and Excel formats are most sensible in case you are going to edit the resulting information, reformat it or include it in complex documents later. In these cases, we recommend that you create a report with no table of contents to make it easier to control links while editing.

Besides, DTM Schema Reporter also allows you to view and print the ER (entity relationship) model for the schema being studied. At the same time, you can study both logical (attribute names) and physical (field names, their types and sizes) data model. The program offers 5 levels scaling levels for the model. In case the user does not like the way tables are located on the model, he can edit both their mutual positions and the positions of the lines corresponding to relationships.

In addition to the main reports (database schema documentation), DTM Schema Reporter also offers the statistical report on data contained in the database. Though this report is not directly related to the database, it gives you important information about each selected table and fields it consists of: the number of records in the table, the number of unique values for a certain field, its maximum, minimum and average values. This information allows developers and administrators (DBAs) to obtain important information about information stored in the database and serves as a necessary addition to information about the database schema.