How to build an easy-to-use database for diagramming?

April 7, 2010 2:15 PM

Best database front-end for creating, editing, and exporting obscure Entity-Relationship-Diagram-like analyses?

I'm a grad student at a research lab and spend a lot of time doing work analyses - a functional analysis that usually ends up creating a lot of Visio diagrams. I'm getting tired of futzing with drawing packages, and want to reduce labor, organize large analyses, support students in learning the theory, and export pretty-looking diagrams for tabletop discussion and publication.

Our work analysis has a reasonably well defined structure that I'm sure could be set up in a database. Elements are related to each other by both nested one-to-many mappings and many-to-many mappings (that Visio can't handle). It's like UML's Entity Relationship diagrams, but with enough relations that one diagram can't show them all at once.

A few colleagues have tried making specialized support tools, but the market is too small to make money and the code base has rusted away (think Visual Basic and Mac OS 9). I'd like to get around this by using an off-the-shelf database front-end tool and only program as much as needed to make an easy-to-use interface.

The catch is that the user interface has to be usable for modeling - which is a mix of data entry, report generation, confusion, and frequent editing.

Any suggestions? Bonus points for inexpensive (or good academic discounts), reasonably standards-compatible (for long-term maintenance) and easy export to diagrams (for pen-and-paper work or publication)

posted by anthill to computers & internet (10 answers total)

I have no idea if it meets all your criteria, but check out E/R Studio.
posted by Confess, Fletch at 2:42 PM on April 7, 2010

Not sure what is the extent of its capabilities, but check out OmniGraffle.
posted by neuron at 3:28 PM on April 7, 2010

At my workplace we either use ERWin, or some of the functions of ARIS (which is probably too enterprisey and expensive to be the right answer).
posted by pompomtom at 4:21 PM on April 7, 2010

I am completely in love with Graphviz. It has different frontends available (I haven't really tried any of them, though), and the map files are easy for humans to work with directly. It doesn't take too much effort to render nice looking maps in a web browser from a database. I'm no expert but if you'd like to talk more about Graphviz feel free to MefiMail me.
posted by Skrubly at 4:21 PM on April 7, 2010 [1 favorite]

I've done some very similar work and haven't found an off-the-shelf package that does what you want. Graphviz is great but not interactive. Grappa is a Java library that let's you display and manipulate Graphviz graphs, it'd be a good place to start.
posted by miyabo at 4:50 PM on April 7, 2010

Microsoft's Visual Studio 2010 (due to release next week) has DGML capabilities and might help you out. It's basically a tool for visualizing graphs created with an XML-derived language - DGML (Directed Graph Markup Language).

Info is sadly a bit sparse right now, but here are a few links that might give you more about it:

http://blogs.iis.net/kateroh/archive/2010/03/20/web-pi-apis-visualize-available-products-hierarchy-with-dgml.aspx

http://blogs.msdn.com/camerons/archive/2009/01/26/directed-graph-markup-language-dgml.aspx

http://blogs.msdn.com/jasonz/archive/2010/02/02/favorite-vs2010-features-dependency-graphs-and-dgml.aspx

Good luck. I hope you find something that works out for you.
posted by ElDiabloConQueso at 6:09 PM on April 7, 2010

Not to long ago I did some reverse engineering for some databases we operate at my community college employer. I wrote up some general findings an opinions on my blog, complete with graphical output. These tools work by inspecting existing databases' schemas, but it should be trivial to define schema via SQL and then point SchemaSpy at it. It would be unusual though, to work with schema directly and never store any data.

One important quick note: GraphViz can be exported to SVG, which can then be processed with XSLT to prettify diagrams.
posted by pwnguin at 8:51 PM on April 7, 2010

OmniGraffle can import GraphViz files [it understands a subset of .dot], and lay them out automatically in a number of different ways. You can then move and edit individual nodes or edges.
posted by James Scott-Brown at 2:24 AM on April 8, 2010

I've been looking for something to produce nice genealogical diagrams programmatically for a long time. I think it's the same class of problem. The closest solution I could work out was using Graphviz as an intermediate processor, but it was a lot of work and I didn't like the graphs it produced. I still haven't found a good solution.
posted by Joe in Australia at 5:59 AM on April 8, 2010

Thanks for the thoughtful answers. I apologize for my confusing question - I'm a bit confused about how to call what I'm looking for. It's clear that Graphviz is the hands down solution for generating diagrams from database exports. It's the database populating and editing activities that I'm more unsure how to support.

I'm happy to learn how to define a database model for our lab's analysis diagrams and set it up in MySQL or something. But that's not worthwhile unless there's an easy-to-use database front-end I can configure to help a non-database-savvy user add occurrences, fill in attributes, set up entity relationships, and so on. A direct manipulation diagram database interface is probably a fantasy, but writing SQL command-line queries is way too complicated. Are there any interface options in between?
posted by anthill at 2:51 PM on April 8, 2010