[ensembl-dev] core schema pdf

[William Spooner]

Hi Matthew,

Thanks for the link; that's a very nice diagram of the Ensembl schema!

Will

On 28 Mar 2011, at 16:22, Matthew Astley wrote:

> 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
> 
> _______________________________________________
> Dev mailing list
> Dev at ensembl.org
> http://lists.ensembl.org/mailman/listinfo/dev

--
William Spooner
whs at eaglegenomics.com
http://www.eaglegenomics.com