[ensembl-dev] core schema pdf

[Matthew Astley]

On Fri, Mar 25, 2011 at 09:08:54PM +0000, William Spooner wrote:

> The easiest way to generate this yourself is [...]

That is essentially what I did to make this,
  http://baked.t8o.org/~mca/ensembl-loutre.20100824.png

except I started from one of our annotation databases which has a few
extra tables.  The result may be useful as it is, or might save some
effort if you follow my layout method.  It is legible if printed on A3
paper.


I started with an empty 'loutre' species database, changed a couple of
columns to avoid errors, then ran foreign_keys.sql .

I also defined some extra FKs where tables still seemed to need links.
This means the result is more connected than the ensembl data model
might expect, but hopefully more useful as a diagram.

> and finally use SQL fairy to generate a PDF;
>   http://sqlfairy.sourceforge.net/

I have tried this on very small schemas, but didn't like the results
last time I tried it on something bigger.  It would probably be good
for hand-picked table subsets?

I suspect that with bigger schemas than ensembl, the delegation to
Graphviz may beat other approaches too.  Hence I think the ensembl
schema lands in a "sour spot" that sqlfairy doesn't handle nicely.


A colleague showed me his results from MySQL Workbench,
  http://wb.mysql.com

but I found it harder to read.


I produced my diagram with DbVisualizer (zero-price edition from
http://www.dbvis.com/) and shovelled tables and edges around by hand,
after starting with whichever automatic layout looked best.

In my experience this manually optimised layout process takes longest
the first time; if it's ever necessary to do it again, it's quicker
and you can improve the layout incrementally.

The automatic layouts are OK for small schemas but can't cope with
tangles.  I guess this is because I have in my head some preferences
about which tables belong together, which the layout algorithm
doesn't.

Then I exported (at 1:1 size!) a .gif, loaded it into the Gimp and
added exclusive arcs and text by hand.


The result is not perfect and it's a tedious process which I wouldn't
repeat for a minor schema revision, but I find it very helpful to have
the big picture on one piece of paper...

HTH,
-- 
Matthew