Re: dia to sql and back

["James K. Lowden" <jklowden schemamania org>]

On 01 Apr 2003 11:27:55 -0800, dialist <pvspam-dialist@hacklab.net> wrote:
> > indeed, but I'm not sure want I want, something very similar to the
> > UML Class would be nice, but with extra database bits for things like
> > constraints, and less non-db fields - fields for uniqueness would be
> > good.
> > 
> > A short re-working of the UML class would be fine.
> 
> Sort of. In Dia-speak it means we need to learn StdProps and rework the
> UML shapes from there. And the resultant abomination should of course
> not be called "UML" at all, but maybe something like "Database
> Diagramming" -- something that implies class-like ER diagrams. This has
> been on my to-do list for... months.

My latest thought ballon on this topic is aggregated SVG shapes.  That is,
if we could define a simple shape and a way to bolt them together, we'd
have something more tractable (to say the least) than a StdProps object.  

Databases require a *lot* of metadata, much more than UML diagrams AFAICT.
 There are user-defined datatypes, rules, domains, constraints, indices,
ownership, permissions, and triggers.  There are multiple useful views:
logical, physical, very physical (Do you want to see the columns in the
order they're stored in the database, or do you want to see the primary
key on top?).  There are views, which look like tables but aren't, and
there are tables that logically exist but are not implemented within the
diagram's domain.  

There are "meta-metadata" issues: Do you want indicators on the diagram
for primary and foreign keys, and if so do you want their column order
somehow reflected?  Do you want relationship rolenames, cardinality, and
optionality reflected on the diagram?  

A unique feature of database diagrams is that lines impart attributes:
Connecting A to B *means* stuffing A's primary key into B as a foreign
key.  Lose the line, lose the attribute(s).  It's not just a copy, either,
if you want to support DRI in the DDL.  

There is a need to name and display subsets of tables, and to rearrange
that subset for printing.  A table may appear in several subsets, and
deleting a table from a subset has no effect on its presence in the model.
 I've used this feature is of course not unique to database diagrams, but
I've used it a lot in my time.  The databases that really need diagrams to
be understood, presented, and discussed often have over 100 tables, and
it's very hard to put all that metadata on the page.  

I don't want to overwhelm you.  I just want to point out that the UML
class is completely insufficient, quite apart from being a technically bad
starting point.  (I think it was Cyrille who called it a "hairball" last
year.)  For better or worse, if you want to do databases properly in Dia,
you need to start afresh.  

I can't do the heavy lifting; it's too much to take on. But I can help
around the edges.  At least I'm set up for that now, and, as you can see,
I have plenty of interest.  

Regards, 

--jkl