Thanks Nick....alas, DBdesigner4 and MySQL workbench won't run on my
ppc mac. I'll have to look into either the Java app or finding
another machine to run it one.
Jim
On Apr 13, 2007, at 4:53 AM, Nick Jenkins wrote:
<snip>
Did you make
this manually or with a schema->graph
tool? If the latter, can you recommend a good open source one? I
have some other database projects where I could use diagrams like
this.
With a schema to graph tool. I _really_ didn't want to create it
completely
from scratch :)
I first had a quick look around, and only saw two database schema
diagrams
for open-source software that were close to what I was looking for,
namely these two:
http://www.dcm4che.org/confluence/display/ee2/Database+Schema+Diagram
http://www.torkiljohnsen.com/wp-content/uploads/2006/04/
joomla_15_database_schema.png
I don't know how the first was created (I've asked on the guy's
blog, but haven't got
an answer yet), but the second was done with dbdesigner4 as per
this blog post:
http://www.torkiljohnsen.com/2006/04/30/joomla-15-database-schema/
- and it was what
was used for this diagram, as Jared correctly guessed.
Dbdesigner4 is open source (GPL), and is available for Windows and
Linux. It works on
MySQL, SQLlite, Oracle, MS SQL, and ODBC databases. ( http://
fabforce.net/dbdesigner4/ )
It seems fairly good to me - it has a couple of minor annoyances
(e.g. you can't export
to SVG, only to PNG or BMP), but probably try it first for your
other projects.
I also had a play with schemaSpy ( http://
schemaspy.sourceforge.net/ ), (example diagram:
http://schemaspy.sourceforge.net/sample/relationships.html ), which
is a Java package,
but I got an error trying to run it under GCJ (rather than Sun's
JVM), so in the end
I just stuck with dbdesigner4.
I see one line, from the hit_counter box,
that appears to run through the revision box
to the page box.
This kind of layout work is hard,
but rerouting the line would probably
be worth the gain in clarity.
Good idea - I have moved the hitcounter table up now so that the
link doesn't go
through the middle of the revision box.
Maybe, if you include documentation
of the tools used to produce the diagram
and provide their intermediate files,
you will be able to get others to help
with revisions when the schema changes.
In terms of what you do to recreate the diagram, using dbdesigner
4.0.5.6, you go to the Database
menu -> Reverse Engineering -> specify the host / database /
username / password, and it will
create a diagram without any coloured boxes, and very few
relationships, and no descriptions,
but with all the database tables.
The coloured boxes you add with the "R" / "new region" button in
the toolbar. The relationships
were added with the "New 1:n Non-identifying relation" toolbar
button (you select it and click
on the source column in one table, then click on the dest column in
the other table, then supply
a description of what the relationship is, and correct the field
name in the destination table,
which it always seems to get wrong, and then it will draw a link
between the two). The text
descriptions of tables were added using the text toolbar icon
(looks like a letter "T" on a bit
of paper), and most of the descriptions came from
MediaWiki.org or
tables.sql.
However, there's no need for anyone to reinvent the wheel doing the
above, just open the saved
XML data file in dbdesigner, and save yourself the hassle - it's
available at:
http://files.nickj.org/MediaWiki/mediawiki-dbdesigner-schema-data.xml
(it's under the same licenses as the image, as per below).
I hope you make this generally available.
...
Please upload to Commons??? :)
Done, now at:
http://commons.wikimedia.org/wiki/Image:Mediawiki-
database-schema.png
I'm not quite sure what license I should be using, but I've
currently put it under both the GFDL
& GPL. The reason I'm not 100% sure what licence to use is that it
is derived from the great
work that others have done (both in MediaWiki's tables.sql, which
is GPLv2, and on
MediaWiki.org,
which is GFDL) in terms of documenting descriptions of tables and
fields that are used for joins.
So if there are any problems with this, please tell me what
license / licenses it should be
under and I will happily change it.
-- All the best,
Nick.
_______________________________________________
Wikitech-l mailing list
Wikitech-l(a)lists.wikimedia.org
http://lists.wikimedia.org/mailman/listinfo/wikitech-l
=====================================
Jim Hu
Associate Professor
Dept. of Biochemistry and Biophysics
2128 TAMU
Texas A&M Univ.
College Station, TX 77843-2128
979-862-4054