How to make a good documentation of a database ?

#1

Hi !

I've just inherited the responsibility of a postgresql database
of roughly 480 tables and 6460 columns, mainly without constraints,
not even foreign keys.

I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another column...).

The best idea I could come with to do that was to maintain an output of
pgdump --shema-only, versioned with cvs, annotated with a patch, itself
versioned with cvs.
Not that bright, isn't it ?

The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).

Does somebody know the right way to do this ?

Best regards,
David Pradier

--
dpradier (AT) apartia (DOT) fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#2

Quote:

The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).

It's a while since I've used them (just coming back to postgres after
enforced exile in mysqlville) but last I checked:
http://gborg.postgresql.org/project/dbutils/
Could generate UML diagrams from your DB. You might still be stuck
diff-ing them when you change the db, but at least they're prettier than
pg-dump.

Matt

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#3

Autodoc might be useful: http://www.rbt.ca/autodoc/

On Nov 18, 2004, at 8:31 AM, David Pradier wrote:

Quote:

Hi !

I've just inherited the responsibility of a postgresql database
of roughly 480 tables and 6460 columns, mainly without constraints,
not even foreign keys.

I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another
column...).

The best idea I could come with to do that was to maintain an output of
pgdump --shema-only, versioned with cvs, annotated with a patch, itself
versioned with cvs.
Not that bright, isn't it ?

The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).

Does somebody know the right way to do this ?

Best regards,
David Pradier

--
dpradier (AT) apartia (DOT) fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98

---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#4

On Thu, Nov 18, 2004 at 03:02:59PM +0000, Matt wrote:

Quote:

The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).

It's a while since I've used them (just coming back to postgres after
enforced exile in mysqlville) but last I checked:
http://gborg.postgresql.org/project/dbutils/
Could generate UML diagrams from your DB. You might still be stuck
diff-ing them when you change the db, but at least they're prettier than
pg-dump.

Thanks for the link, it's interesting :-)
Not totaly perfect, still :
I'll have to modify the schema of the database a lot during the next
months, so I'd prefer not to have to do a lots of diffing between the
database description and the 'comments documentation'.

Maybe the perfect solution doesn't exist yet ?

--
dpradier (AT) apartia (DOT) fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#5

David Pradier wrote:

Quote:

In fact, I have already looked a little into the tables of PostgreSQL itself
to see if I could hack it by adding a column "Comments" in the "table of
columns".
But I guess it isn't wise nor feasible, is it ?

You are familiar with:
COMMENT ON TABLE t IS 'this is my table';
And \d+

The comments get dumped with their associated object too.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#6

What we are doing here is likely not applicable to you, but I'll still
tell it:

- keep the data definition in an XML document, which includes all the
comments about all the tables/fields in the schema;
- generate both the database schema and the (HTML) documentation out of
this XML using style sheets;
- keep the XML in CVS for version control;

The HTML docs are also diffable between versions, but we don't keep them
in CVS as they can be readily generated from the version controlled XML.

When we have to update our schema, we update the XML data definition,
and regenerate the docs. We also have an XML schema which makes
table/field descriptions mandatory in the XML, so the busy developer
will not forget them.

Now it's clear that this needs a quite elaborate framework, and it took
some time for us to make it work, and it doesn't work always without
problems, but it is an option. For us the main reason to do it this way
was that we could abstract the XML such that we generate the schema for
Oracle and Postgres from the same data. But this "multi-db" part of it
is quite hard to achieve/maintain, and if you target just postgres, it
is somewhat easier.

HTH,
Csaba.

On Thu, 2004-11-18 at 17:11, David Pradier wrote:

Quote:

On Thu, Nov 18, 2004 at 03:02:59PM +0000, Matt wrote:
The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).

It's a while since I've used them (just coming back to postgres after
enforced exile in mysqlville) but last I checked:
http://gborg.postgresql.org/project/dbutils/
Could generate UML diagrams from your DB. You might still be stuck
diff-ing them when you change the db, but at least they're prettier than
pg-dump.

Thanks for the link, it's interesting :-)
Not totaly perfect, still :
I'll have to modify the schema of the database a lot during the next
months, so I'd prefer not to have to do a lots of diffing between the
database description and the 'comments documentation'.

Maybe the perfect solution doesn't exist yet ?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#7

On Thu, Nov 18, 2004 at 04:22:28PM +0000, Richard Huxton wrote:

Quote:

David Pradier wrote:
In fact, I have already looked a little into the tables of PostgreSQL
itself
to see if I could hack it by adding a column "Comments" in the "table of
columns".
But I guess it isn't wise nor feasible, is it ?

You are familiar with:
COMMENT ON TABLE t IS 'this is my table';
And \d+

The comments get dumped with their associated object too.

No, I was not :-)
Wow, thanks Richard !
It's exactly what i wanted.

"Prepare to revive foul ghosts of foreign keys,
Prepare to die horrible doubts of the database pit,
Light is to be unleashed on you soon
and great is the relief shining on me."

David Pradier

--
dpradier (AT) apartia (DOT) fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#8

dpradier (AT) apartia (DOT) fr (David Pradier) writes:

Quote:

I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another column...).

Would Druid (http://druid.sourceforge.net/) help?

Bill
--
Bill Harris
Facilitated Systems
http://facilitatedsystems.com/

#9

dpradier (AT) apartia (DOT) fr (David Pradier) writes:

Quote:

I've just inherited the responsibility of a postgresql database
of roughly 480 tables and 6460 columns, mainly without constraints,
not even foreign keys.

I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another column...).

The best idea I could come with to do that was to maintain an output of
pgdump --shema-only, versioned with cvs, annotated with a patch, itself
versioned with cvs.
Not that bright, isn't it ?

The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).

Does somebody know the right way to do this ?

We use Rod Taylor's "postgresql-autodoc", which is a Perl script that
rummages through a database schema and generates output in several
forms.

The form that we're finding most useful is the HTML form, where it
generates an HTML table for each table, annotated with any COMMENTs on
tables/columns, as well as indicating linkages between objects.

The capabilities for generating "pretty pictures" are a bit limited,
but it does reasonable cross-referencing in the HTML form...
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.

#10

dpradier (AT) apartia (DOT) fr (David Pradier) writes:

Quote:

Well, yes, that's what I currently use.
The dot output is very interesting, but I guess the complete database
image will be 16 meters x 16 meters large when I have finished adding
the constraints.
I have already stopped to print it.
That's why I'd like to make something more 'paper-y' like 'The book of
my database', with lots of comments.

Or, I could insert the comments in the database itself so that autodoc
auto-documents them :-)
In fact, I have already looked a little into the tables of PostgreSQL itself
to see if I could hack it by adding a column "Comments" in the "table of
columns".
But I guess it isn't wise nor feasible, is it ?

Not only can you put comments on tables, but you can put comments on
columns.

From the Slony-I sources:

comment on table @NAMESPACE@.sl_setsync is 'SYNC information';
comment on column @NAMESPACE@.sl_setsync.ssy_setid is 'ID number of the replication set';
comment on column @NAMESPACE@.sl_setsync.ssy_origin is 'ID number of the node';
comment on column @NAMESPACE@.sl_setsync.ssy_seqno is 'Slony-I sequence number';
comment on column @NAMESPACE@.sl_setsync.ssy_minxid is 'Earliest XID in provider system affected by SYNC';
comment on column @NAMESPACE@.sl_setsync.ssy_maxxid is 'Latest XID in provider system affected by SYNC';
comment on column @NAMESPACE@.sl_setsync.ssy_xip is 'Contains the list of XIDs in progress at SYNC time';
comment on column @NAMESPACE@.sl_setsync.ssy_action_list is 'action list used during the subscription process. At the time a subscriber copies over data from the origin, it sees all tables in a state somewhere between two SYNC events. Therefore this list must contains all XIDs that are visible at that time, whose operations have therefore already been included in the data copied at the time the initial data copy is done. Those actions may therefore be filtered out of the first SYNC done after subscribing.';

(@NAMESPACE@ gets transformed into a namespace name via a sed script;
use your favorite namespace as needed...)
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.