Dia and Database Design

[Tim Ellis <Tim Ellis gamet com>]

> >> Did you see the mail by Matthew Mondor about database design objects
> >> <20020727095425.GA31756@calixo.net>?  You'd be better qualified to
> >> answer him than I.
> > 
> > I've searched my mailboxes and cannot find any reference...?
> 
> It was forwarded by Cyrille, that's why I gave the msgid.  See
> http://mail.gnome.org/archives/dia-list/2002-July/msg00286.html

My mailer is lame and can't search on msgId. Thanks for the URL.

Matthew Mondor writes:

> Dia is a wonderful program which I am very happy to use for various
> tasks. There is one thing which I tried to do with it but which seems to
> be really hard (there would be missing a few simple tools to do it):
> designing databases consisting of tables, with key/index/normal field
> list for each...
> 
> There could be a simple object, creating a box with a title, and with
> two columns such as:
> 
> +--------------------------+
> | USER                     |
> +-----------+--------------+
> * user_id   | bigint(11);  *               (a key)
> * user_adm  | bigint(11);  *               (a reference index)
> | user_nick | varchar(32); |
> | user_pass | varchar(32); |
> +-----------+--------------+

Matthew,

Use the UML shapeset in Dia. Tables and Views are represented as Classes.
Foreign key relationships as Associations. Any Class attribute marked as
"protected" will become part of the primary key.

Then, use either tedia2sql: http://tedia2sql.tigris.org or dia2sql (search
Freshmeat) to convert your ERD to SQL DDL for your target database.

Even though I'm the author of tedia2sql (thus, surely, biased), I'm going
to strongly recommend you use it instead of dia2sql. Check both out if you
don't believe me. Read the documentation at the homepage I've given you
and I think you'll agree that using tedia2sql with Dia will make you
pretty happy designing ERDs.

Here is a short list of tedia2sql features that I think make it better
than dia2sql:

 * Associations == Foreign keys, not some wierd attribute naming
   convention (which I never fully understood myself)
 * SQL '92 Datatypes automatically converted to target RDBMS datatypes
 * Indexes
 * Views
 * Permissions (grants)
 * Inserts to populate tables with initial data (ie: code tables)
 * Oracle, DB2, Postgres, Sybase target databases (others easily added)
 * Script is GPL (libre and free!)

Go look at the tedia2sql screenshots at the tedia2sql homepage, and I
think you'll get the idea.

-- 
Tim Ellis
Senior Database Architect
Gamet, Inc.