Re: dbschema and documenting tables/columns

>From: uunet!col.hp.com!judym (Judy Miller) >Subject: dbschema and documenting tables/columns >Date: 23 Jul 92 16:49:57 GMT >X-Informix-List-Id: <news.1574> > >I need suggestions for documenting an Informix database I support. It >has been modified greatly since it was first created. I used the dbschema >utility to create a fresh schema. The problem is the original sql files >contained lots of valuable documentation. > >Is Informix self-documenting (meaning you can add descriptions somewhere >in the database), or can someone suggest a method where I can make it >self-documenting? (or I'll invent my own...) > >I'd like the descriptive info to be part of the database, not a separate >file that must be maintained. DB2 has a comments column in its equivalent of Systables and Syscolumns, which can be used to store the extra information -- Informix does not. This means that if you are going to keep the comments in the database, you will either have to play with the system catalogue (and you'll be all on your own when everything falls into a heap around your feet -- it would NOT be supported), or you provide your own data dictionary tables, or you buy a third party data dictionary system (there must be some out there, but I don't have anything in mind). If you decide to write your own data dictionary, bear in mind that tables can be altered, and that Systables.Tabid usually changes when you alter a table. This makes it unsuitable for use as the primary key of your data dictionary table description table. You will probably end up defining your own equivalent of Tabid, with table name as an alternative key, and the actual tabid as a foreign key. Similarly, columns can be moved around in the table, so the Colno attribute is not necessarily as useful as it could be. And both table names and column names can be altered. The point I'm making is that the system catalogue can change without your data dictionary being aware of the change, and if your dictionary is too closely tied in to the key structures in the system catalogue, you will get erroneous information out of your data dictionary after the database is changed. Which version of Informix are you using? If you are using Version 5.00, you can declare PRIMARY KEYs, FOREIGN KEYs and CHECK constraints. These are often part of the information which would exist as comments, but they can be documented in the database explicitly, which is a step in the right direction. It does not allow you to document the purpose of a table or column, though, so you still need some data dictionary support. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>