[thelist] Database documentation

[rudy]

> As is the case with most developers, I think that my database
> is clear and well structured (the file/table names are explicit, etc..),
> but at the moment, absolutely no documentation exists for the
> databases or for the ASP pages (except for comments within the code).

hi norma

what a great question

there are two points i would like to make

first, for a database, it is very common to have
a data model diagram

for example,

   http://members.evolt.org/rudy/oracle4.gif

(warning:  this diagram is badly out of date)

table/column naming conventions are important, but
understanding data relationships is the   sine qua non
of maintaining (i.e. structurally changing) a database-driven
web site

the second thing i would like to point out is much deeper
(or perhaps obscure)

it involves a philosophical approach to the purpose of
documentation in general

documentation is supposed to make life easier for those
who come after us and who need to make structural changes

the question arises:   what happens if the documentation
is out of date?

in the case of table/column names, there is a most interesting
twist here

you can usually query the database to get table layouts --
these are kept in system tables, often called the system calatog
or data dictionary

for example, in the evolt database, you can get table layouts using
oracle's DESCRIBE command --

 describe categorys
 Name        Null?    Type
 ----------- -------- -----------
 CATEGORYID  NOT NULL NUMBER(8)
 CATTYPEID   NOT NULL NUMBER(8)
 CATEGORY    NOT NULL VARCHAR2(50)
 CREATED     NOT NULL DATE
 CREATEDBY   NOT NULL NUMBER(8)
 ACTIVE      NOT NULL NUMBER(1)
 ACTIVEBY             NUMBER(8)
 DATEMOD              DATE

so on the face of it, there is little downstream benefit to those
who come after us to document stuff that they can simply
help themselves to

further, additional information may also be obtained by
querying the tables themselves

for example, if you look at the evolt site, check the dropdown list
of categories at the right side of the black tab across the top of
each page

it is one thing to have documentation that tells you this dropdown list
is generated from the CATEGORYS table, and it is another thing to
run a query against the CATEGORYS table and discover that there
are categories that do not appear in the dropdown list

thus, the focus of documentation should not be on something as
routine or easy to obtain as table/column names, but more on things
like *exceptions* to what a competent maintenance person would be
able to surmise with some digging

the purpose of documentation is to minimize the digging

;o)

finally, i want to say something about garbage

allowing garbage to accumulate is one of the worst things you can do
to those who follow you

in my opinion, garbage is worse than an absence of documentation

"garbage" is the term i apply to anything in the live site that isn't
actually live

if i spend two days trying to figure out how the logic in the  index.html
page works, only to discover that the web server automatically serves
up home.html instead and that index.html stopped being the live page
a long time ago, i'm gonna be one very unhappy camper

please -- be ruthless in removing test pages, old versions, and any
unnecessary components of your application from your live directories


rudy
http://rudy.ca/