PostgreSQL and Data Modelling Tools

Andrew Hallam | | 7 September 2007, 20:52

The opportunity recently presented itself to reverse engineer a PostGIS database using three different data modelling tools. All tools connected to PostgreSQL via an ODBC connection. None handled the PostGIS user defined “geometry” type as well as desired.

The goal was to quickly reverse engineer a schema and then be able to generate a new schema from the extracted metadata with full fidelity. Keep in mind that the user of these complicated tools was a complete novice with each of them. Caveat emptor.

Here is what they thought the data type of a geometry column should be:

ERWin (CA) geometry(255)
ER/Studio (Embarcadero) CHAR (default data type)
Data Architect (theKompany) nothing

Embarcadero and theKompany appear to be willing to investigate support for user defined types, which would be great for PostgreSQL users. Computer Associates were not contacted.

First Impressions

See “complete novice” disclaimer above.

ER/Studio was the easiest to pick up, and seemed to have all the bases covered. It handled reverse engineering an Oracle database with 692 tables and about 7000 columns with no problem.

ERwin appears to be the most powerful, but the user interface was complicated. You probably need to use it every day to get the benefits. Connecting to a database was not as straight forward as it should have been.

Data Architect was not as inuitive as the others. The online help was thin. e.g. Connecting to PostgreSQL databases was something this user found difficult to reliably repeat. But then, Data Architect is costs less than 10% of the high end tools.

Comment [3] »

  1. Oracle JDeveloper can connect to a postgres/postgis database using JDBC and correctly identifies geometry columns as having the type geometry. I found it to be a reasonably easy tool to use, somewhat quirky but quite functional. Data modeling is only one small aspect of what it does, I haven't used any of the other development functions as I use Eclipse for that.

    Chris Hodgson — 8 September 2007, 13:59

  2. Hi Chris,

    Thanks for the heads up on JDeveloper. The reason I didn't initially consider JDeveloper was advice that Oracle tools can read metadata from other databases, but cannot write PostgreSQL DDL. More testing required.

    Andrew

    Andrew Hallam — 8 September 2007, 19:17

  3. I use and love Sybase Power Designer. I have not tried it with PostgreSQL, but it works very well with Oracle Locator - identifying the SDO_GEOMETRY columns without a problem.

    I have not used ER/Studio nor Data Architect, but have used ERwin and couldn't agree with your statements more. Really felt the interface stunk.

    Jeff Hobbs — 20 September 2007, 21:00

Commenting is closed for this article.

|

Powered by Textpattern | Tranquility White made TXP-ready by Textpattern Templates