Database administration and schema design tools

This is a comprehensive review of database administration/management software and of visual schema design/modeling tools, done in May 2008. I published key results from this analysis on Wikipedia, at Comparison of database tools.

Generic database support

Happy Fish 4

  • - hangs for about 30 seconds upon startup if creating a new diagram or loading an old one
  • - can only connect thru data providers (ODBC), not directly
  • - must save password for connection to succeed
  • + can reverse engineer, detect foreign keys, and draw graph (badly)
  • ++ when reverse engineering, preserves user's modifications of the diagram and only imports new tables
  • - when reverse engineering, doesn't remove tables from the diagram that are no longer in the DB
  • + relationships can be moved on the contour of the table and can originate from the proper field
  • + groupboxes
  • - relationship lines are only orthogonal
  • + can dray many-to-many relationships and automatically create the junction table
  • - in the diagram, columns of a composite PK with every member also a FK, are only displayed as PK columns
  • - unique constraints must be entered manually
  • + can forward engineer via SQL from diagram which can be execute by the 'Execute SQL' window

DeZign for Databases

  • - slow 30s startup
  • - trial crippled to max 8 attributes per entity
  • + can reverse engineer to auto-generate diagram; junction tables have rounded corners
  • + selective/incremental reverse engineering via File->Update model from database
  • + forward engineering via 2 generated scripts: drop and create
  • - diagram relationship lines are only orthogonal and ends can be moved only on one side of the table
  • + subdiagrams!
  • + groupboxes
  • + individual table or line formatting
  • + can create many-to-many relationships with junction tables
  • - no table data manipulation; no ongoing connection with the database; strictly a modeling tool

SquirreL SQL 2.6.5a

  • + incremental reverse engineering only, by adding each table to the graph (right click on the table)
  • + can add folding points to relationship lines
  • - relationship lines start supposedly from the table edge near the FK, but fail; are always oblique and endpoints can't be moved
  • - can't change formatting for tables or relationship lines
  • - graph looks stark and ugly; no zooming except for printing

Hint: to connect to a database, go to the Alias tab and click the + toolbar icon.

TOAD Data Modeler 3 beta; 2008-05-29

  • -- serious problem with duplicate items after reverse engineering PostgreSQL 8.3.1: duplicate table columns, user groups, key fields, sequences, tablespaces etc.
  • - key fields get duplicated while editing a new entity, as well
  • + selective forward engineering (uncheck "Generate by property")
  • + apparently selective/incremental reverse engineering of only selected items, but too buggy to work
  • + can verify model (which ironically, finds duplicate attributes)
  • - can't modify position of relationship line endpoints
  • - table permissions added in awkward fashion (must click "Add permission" on table until desired user's permission are added, then delete permissions for unwanted users) VERDICT: rejected (too buggy, too modeling-oriented)

Aqua Data Studio 6.5.8

  • + powerful user manager: object permissions for individual tables, views, functions, all in one place
  • + best separation of system tables and views from regular tables and views
  • + comprehensive access to system indexes, operators, datatypes etc.
  • + can reverse engineer multiple schemas into one ER diagram
  • + selective reverse engineering of specific tables
  • - can't forward engineer because can't alter physical entities in the ER diagram, nor update them if altering them via the DB explorer
  • - if adding tables via Create Table, can't add them to the diagram except by regenerating it, which loses any user modifications (resizing and repositioning of entities)
  • - relationship lines can be disjointed from tables (by dragging) and become orphan
  • - the table creation dialog doesn't directly allow the specification of primary keys or UNIQUE constraints
  • + nice visual query builder, and can reorder SELECT items (right click, move up/down)

NOTE: The current version is 7.0 (not evaluated yet), which has many new features, including an ER modeler.

v7.0:

AnySQL Maestro

  • - pretty much like PostgreSQL Maestro but doesn't draw FK relationships when reverse engineering
  • - access is through ODBC; no database-specific features

SQL Developer

  • - lists indexes as tables (!)
  • - can't create tables (!); the "New" context menu entry is disabled, but it's enabled for Views, for example
  • + can import tables from clipboard (and create them, despite the above)
  • + selective reverse engineering with automatic layout
  • - diagram relationship lines start from the contour of the tables and can't be altered

SQLPro

  • + only 1.2Mb
  • -- can't connect to PostgreSQL because some idiot error with SSLEAY.DLL (SSL was disabled)

SQL Edge

  • + reverse engineering
  • + "The connections start from the primary key (master table) columns and point to the foreign key (detail table) columns."
  • + cool aligning of currently dragged table in the diagram to instant lines edging existing tables
  • - can't change relationship lines at all
  • -- annoying pop-up with 4 shortcut icons, appearing whenever the mouse stops moving
  • --- can only create and drop (from the Schema perspective), but not alter tables

DBVisualizer 6.5.4

  • - direct download bypassing registration nags: http://minq.se/products/dbvis/download/install.jsp
  • + no installation required, just unzip
  • + autosets maximum column size when viewing tables, unlike PgAdmin or Navicat (what is so difficult about that?)
  • ++ reverse engineering (the "References" tab) with nice-looking ER graph
  • + relationship lines automatically start from the corresponding field of the table
  • - can't add a new table from the DB to the graph and keep the existing user-modified diagram (graph redraws itself)
  • - bad UI on altering tables (tedious specification of keys)
  • - bug: when creating a foreign key, the selectors for the referenced table are Database, Schema, Table, but "Table" contains tables as well as sequences and primary keys.
  • - bug, continued as above, the selectors for the reference column are even worse (column names, sequence names etc., mixing in system functions as well). This also means you'll have an "id" from each table that has one, but you won't know which!
  • - bug: in situation above, among the schemas there is still 'public', which had been deleted and doesn't appear in the explorer
  • - bug: when selecting actual tables from the selector, the column drop-down becomes empty
  • - apparently can't extract/export database schema and all table definitions
  • - slow reverse engineering and reference graph creation (20 seconds for 7 tables, 10s for 4)
  • - Java crashes on accessing DDL for a table with references; frequent Java crashes
  • - no user administration features (can only view users properties)
  • + supports SQLite via JDBC

Notes: best auto-graphing

Database Design Studio Pro 2.21.3

  • - frozen in 2005; last specific PostgreSQL version supported is 7.4
  • - requires libpq.dll, which after being copied from pgAdmin to the app's source dir, AND to c:\windows, still isn't found
  • + option to convert sequence fields to "serial"
  • - connection thru ODBC takes ODBC's credentials without possibility of overriding. This means you have to save the DB user/pass in the ODBC dialog.
  • -- reverse engineering dies with pg_relcheck does not exist

SQL Diagrams

  • - no PostgreSQL support yet

fabForce DBDesigner 4.0.5.6

  • -- frozen in 2003; bought by MySQL and became MySQL Workbench, crippled to support only MySQL
  • + works with PostgreSQL via ODBC
  • + looks pretty
  • -- reverse engineering PostgreSQL via ODBC doesn't detect foreign key relationships
  • -- can't forward engineer (e.g. update DB after adding a table) due to PostgreSQL syntax error
  • -- annoying windowing in Delphi or something (causes dialogs to stay on top of all other running applications)

Gaudi

  • - Java-based but packaged such that needs a Linux host to run (couldn't get to run on Windows)
  • - pollutes the DB with a pg_m16e_notes table upon connection
  • - development frozen in 2006-June
  • + supports PostgreSQL
  • - can only reverse engineer tables, not foreign key relationships among them

PyDbDesigner

  • -- frozen in 2004
  • - reverse engineering was "planned"

MySQL Workbench 5.0.21 OSS

  • -- MySQL specific
  • + pretty UI but not very intuitive

StarUML; 2007-02-07

  • - Maybe good for UML but can't draw a simple ER diagram

eGlop Database Schema Designer

WWW SQL Designer

  • - rewritten from scratch and can't export PostgreSQL

Database schema designer

  • - how the heck do you use this?
  • - rudimentary

PostgreSQL-specific

PostgreSQL Maestro 8.3

  • + can extract database schema and table definitions (Tools -> Extract database, add mutiple objects types)
  • + can reverse engineer but graph looks unpretty; must reconnect to DB if updates had happened for FKs to be detected (just Refresh isn't enough)
  • + relationship line origins can be dragged to start from the correct table field
  • + relationship lines can be broken (press Ctrl and drag from the middle of the line)
  • + incremental reverse engineering (simply add a new table from the DB to the diagram and keep the existing user-modified diagram)
  • + full-featured user manager but split between Roles in explorer, and individual table permission per each table
  • - bug when renaming a table in diagram: double-click table, click "Rename table" in the General tab, Commit. Go back to the designer and notice the table name has not been updated. Trying to add the new table does not work. The old table must be removed from the diagram first.
  • + can alter table structure and data; can add rows
  • - no groupboxes
  • - no individual table or line formatting (coloring)
  • + diagram grid autoexpands when tables are dragged outside its surface
  • - can't rename sequences. PgAdmin III can
  • - can't change the type of serial fields to integer. Selecting "integer" does not perform any change. The sequence field is grayed out regardless of the type selected. PgAdmin can delete the sequence and change the type.
  • - stupid bug: doesn't display NULLs in LEFT JOIN-ed VIEWs (screenshot)

DreamCoder for PostgreSQL 1.5

  • ? Click 'Schema' in the accordion tabs to the left to access the schema
  • - Query builder didn't read any fields of a table with 5 columns
  • - junk blank feedback form after uninstall at http://www.sqldeveloper.net/phpQ/index.php

EMS SQL Management Studio for PostgreSQL -> SQL Manager

  • - annoying MDI interface with hidden windows
  • - no diagramming at all
  • - overly heavy, expensive and complex for what it does
  • - built out of ~10 separate applications
  • - leaves some crap Agent process running

MicroOLAP Database Designer for PostgreSQL 1.2.4 beta

  • + can reverse engineer (10s for 5 tables) and auto-draw graph
  • - can't refresh (useful if underlying database had changed); can only reverse engineer the entire database
  • - relations start randomly from the originating table, instead of from the foreign key, and cannot be moved
  • + sloped relationship lines in the diagram
  • - bug: empty 'visits' schema in the database was not detected
  • - bug: 'public' schema was detected even though public had been dropped
  • - Table Editor doesn't support the 'serial' PostgreSQL type
  • - can create N:M relationship and auto-create junction table to references, but buggy: references don't point to the generated fields, which have dubious names ('id' and 'NMID'), and different types (int4 and serial)
  • - Table Editor does not display the foreign keys; they are only represented in the diagram
  • - Column/Constraint/Index Manager only displays the table of the selected column/constraint/index, but lists all columns/constraints/indexes
  • + forward engineering, with individual table selection
  • - (as a result) forward engineering doesn't handle correctly renaming tables: renames are performed as creating a new table with a different name but the same constraint as the old table, which will generate an error due to the fact that the constraints already exist
  • - (as a result) false impression that editing a table actually saves the changes
  • - no user manager

Navicat 8 for PostgreSQL

  • + native support for PostgreSQL
  • + intuitive UI
  • + good Visual query Builder
  • - ugly relationship lines between tables in the query
  • + detects foreign keys
  • -- no E-R diagramming / schema design
  • + claims: report builder, scheduled synchronization etc.

SQLite-specific

SQLite Administrator

  • - annoying scrolling (unproportional scrollbar thumbs when paging through large amounts of data; only 3 scrollbar thumb positions: top/middle/bottom; unproportional horizontal thumb on tables with many columns)
  • - doesn't display the number of records returned by a search
  • + freeware

SQLite Maestro

  • $80 and up, with 30-day evaluation
  • - does not render UTF-8 encoded text in TEXT fields (text is rendered as its constituent bytes)

SQLite Studio 1.1.3

  • built with TCL/Tk, which crashes sometimes

Thanks!

Buy me a coffee to sponsor more cool posts like this!

My tags:
 
Popular tags: