Database Documentation
Proper documentation of your database is the most important aspect of Visual Basic database application development. Even if you completely ignore all of the guidelines listed in the earlier "Database Design" section, adequate documentation will ease the burden of database integration. However, if you lack proper or complete database documentation, you are asking for headaches, especially when you need to modify your application years down the road.
There are several specialized tools to help you in the documentation of your database. The most common of these tools lets you craft entity-relationship diagrams. These diagrams look a little like the Relationships window within Microsoft Access, although they specify much more information about each entity (similar to tables). Some advanced entity-relationship modeling tools, such as Logic Works' ERWin, give you absolute control over every aspect of the logical and physical layout of your data-base. They can reverse-engineer existing databases, create databases or scripts for new databases based on a model you design, and can store platform-specific information, such as stored procedure source code. While these tools are very useful for database design and documentation, their features often come at a financial price that may be prohibitive for the individual developer.
Instead of an entity-relationship modeling tool (or as a complement to one), you can document all aspects of your database within your technical documentation, specifically the Resource Kit described in Chapter 5, Documentation. Include a section within the Resource Kit that presents all the tables and views of your database in alphabetical order, identifying each field, and all subordinate objects (such as triggers, indexes, and sequences). Create a separate Resource Kit section for those database objects that are not specifically dependent on a table or view (such as stored procedures).
Figure 12-1 shows sample Resource Kit documentation for a set of tables used to track baseball teams, players, and player positions.
Figure 12-1. Sample database documentation.
There are several things to note about this documentation.
Each table identifies in its description the foreign references in which it appears.
All fields are described in detail, providing names, data types, descriptions, valid data ranges (if any), required or optional status, and foreign-key relationships. Obsolete or unused fields are clearly marked.
The data types indicated for each field are somewhat generic. For instance, the Team.FullName field might be created as Text(50) in Microsoft Access, as var-char(50) in Microsoft SQL Server, and as VARCHAR2(50) in Oracle.
Subordinate objects are documented immediately following the table.
If you modify the structure of the database, the documentation must be immediately updated to reflect the current state of the database. If you are making changes to a test database for later inclusion in a production database, this section of the Resource Kit should reflect the latest changes to the test database. Include an additional section within the Resource Kit that indicates what database object changes must be made from one structural revision to the next.
The Resource Kit is an excellent place to discuss the formats of simple database types, such as text files or spreadsheets. If your application accepts or generates data in one of these or similar formats, include a section in your Resource Kit that describes the expected structure of the file in detail.
If your database depends on any vendor-specific or database-specific files, configuration information, or connection requirements, document them in the Resource Kit as well. What should not appear in your Resource Kit are the full source code lisings for database stored procedures and triggers. Make note of these objects in the Resource Kit, then store text-file-based versions of these objects in your source code directory tree.
If your Visual Basic application will process data found in an existing and actively used database that is already fully documented, it is not necessary for you to replicate the existing documentation. Include in your Resource Kit enough documentation to redirect the reader to the location of the full database documentation. If you will be using only a subset of an existing database, at a minimum list the names of the database objects employed in your application, then direct the reader to the full documentation set.