Thread: Tool for database design documentation?

Tool for database design documentation?

From

"Jon Christian Ottersen"

Date:

30 July 2005, 19:20:23

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?

Re: Tool for database design documentation?

From

Steve Manes

Date:

30 July 2005, 19:29:36

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?

I prefer to use a third-party tool like Case Studio 2
(http://www.casestudio.com) for this.  Import your schema into CS,
document your various entities in the table/element/key/etc popups, then
Export everything to HTML.  CS generates pretty complete documentation.

Re: Tool for database design documentation?

From

Jeffrey Melloy

Date:

31 July 2005, 05:54:26

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

Re: Tool for database design documentation?

From

William Bug

Date:

01 August 2005, 03:22:30

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

feeding big script to psql

From

Havasvölgyi Ottó

Date:

02 August 2005, 01:11:29

Hi,

A generated a big SQL script (about 20 Mb), and fed it to psql. I was very
surprised that within a minute psql became quite slow. There were areas,
where less than 10 row were inserted in a second.
This is on a WinXP machine with local server 8.0.3, and only I use it.
Looking at the log files of PG the commands are executed fast.
I have monitored psql with te TaskManager, and it was at 45-50% CPU (this is
Hyperthreaded, so 50% is the max for one thread), and the memory
allocation/deallocation was very active, even about +-2Mb/sec.
The command were simple create table and insert commands chunked into
several lines like this:

CREATE TABLE aeloleg(
vevo CHAR(6),
szallito INTEGER,
datum DATE,
hatarido DATE,
vevo_nev CHAR(30),
ir_szam INTEGER,
helyseg CHAR(20),
cim CHAR(20),
befizetes INTEGER,
terheles INTEGER,
hitel INTEGER,
rendeles INTEGER,
jel CHAR(1),
trans INTEGER,
szoveg TEXT,
storno BOOLEAN) WITHOUT OIDS;

The insert commands for one table were surrounded by BEGIN and COMMIT like
this:

CREATE
BEGIN
INSERT
INSERT
...
INSERT
COMMIT


I know it would be faster with COPY, but this is extremly slow, and the
bottleneck is psql.
What is the problem?

Regards,
Otto