Re: Tool for database design documentation? - Mailing list pgsql-general
| From | William Bug |
|---|---|
| Subject | Re: Tool for database design documentation? |
| Date | |
| Msg-id | 3F486FC5-D992-48F1-86BA-36F45516FD0F@drexel.edu Whole thread Raw |
| In response to | Re: Tool for database design documentation? (Jeffrey Melloy <jmelloy@visualdistortion.org>) |
| List | pgsql-general |
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
pgsql-general by date: