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@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