Re: Tool for database design documentation?

I use a combination of the gnome-based diagraming application Dia
(http://www.gnome.org/projects/dia/) and the tedia2sql diagram-to-DDL
SQL translation script, which is written in Perl (http://
tedia2sql.tigris.org/).

It's a bit awkward, but:
1) Dia is a wonderful, open source diagramming tool I can run on
Windows, Mac OS X (via Fink) or Linux - nearly all of the commercial
tools have traditionally been Windows only, though this is slowly
changing;
2) Dia comes with a set of UML diagram objects which work quite
well for laying out a detailed data model. There are also ERD
diagram objects, they don't seem to be of much use;
3) tedia2sql does a very good job translating most standard
SQL-92 (and some additional SQL-99 extensions) from UML objects to
SQL objects;
4) Dia saves its diagrams in XML format which makes it
relatively straight-forward to add to the SQL translations being
performed (see the many tedia2sql like translators on the Dia links
page - http://www.gnome.org/projects/dia/links.html). Some of the
PostgreSQL specific features such as Inheritance and Rules can be
diagramed using the UML formalism and one can write extensions in
Perl to tedia2sql to turn that in DDL SQL;

There are other SQL to Dia diagram translators (e.g., PostgreSQL
AutoDoc [http://www.rbt.ca/autodoc/]) which allow for reverse-
engineering a data model diagram from an existing PostgreSQL DDL SQL
schema. There are also other Dia-to-SQL tools (see the Dia links
page), some of them even more specifically tailored to PostgreSQL
than tedia2SQL is. I came to using tedia2sql over these because I
found it supported more of the general SQL entities and relations I
needed and it was relatively easy to use.

I should add this approach of sticking with open source tools that
are relatively straight-forward to modify and extend is not for the
faint of heart. You absolutely must have a solid grasp on what a
database Entity-Relation Diagram (ERD) is. Understanding the
translation process (XML Dia diagram objects to DDL SQL code
generation) can be quite helpful in making best use of tedia2sql,
though it is not necessary to getting started turning DDL SQL out
from your Dia data models. A thorough understanding of this process
and knowledge of XML document parsing IS required to extend the
translator.

This more "home grown" solution doesn't do as good a job separating
logical data models (implementable in any RDBMS) from physical data
models (version of the logical model designed for a specific RDBMS).
I've found to easiest and quickest to simply create the physical
model diagram and focus on how to get that translate properly in
PostgreSQL DDL SQL. The Dia data model diagrams I create aren't
easily re-usuable in other RDBMSs, but then again, I don't intend to
stop using PostgreSQL, unless I'm driven from it for some
applications-specific reason.

I'm working on a port of tedia2sql to Ruby (my scripting language of
choice of late), mainly because I've got Object-Relational mapping
libraries there which will make it easier to create a complete,
"round trip" ERD system - i.e., be able to both generate Dia diagrams
form existing PostgreSQL DDL SQL schemas and turn Dia diagrams in to
DDL SQL that can be used to directly instantiate the schema in
PostgreSQL (via DBI code).

Cheers,
Bill

On Jul 31, 2005, at 1:54 AM, Jeffrey Melloy wrote:

>
> On Jul 30, 2005, at 2:30 PM, Jon Christian Ottersen wrote:
>
>> We are trying to find a good way to document our database design –
>> what is the rationale behind each table/field, what kind of
>> information is each field supposed to contain, perhaps also
>> something about the linking between the tables etc. Is there
>> anybody who has some experience on this? Is the built in
>> ‘comments’ fields in pgsql the best tool or are there any special
>> tools that would be recommendable?
>>
>>
>
> Druid (http://druid.sourceforge.net) works fairly well. (Though
> the interface is abysmal, it does its job). It makes javadoc-style
> documentation for tables and columns.
>
> Jeff