Tool for automatically generating and laying out an entity relationship diagram?
Solution 1:
As long as you've got foreign keys in your database I've found that Visio does a pretty good job. I had a postgresql database with about 150 tables from four different merged projects that were connected through various foreign keys and it did an awesome job of extracting all the relationships and grouping the tables together. The diagram had only a few overlapping lines despite extensive foreign keys. Also, because of the foreign keys logical elements were grouped together nicely it was clear which databases most of the tables originated in.
Solution 2:
SchemaCrawler automatically generates diagrams from databases, using GraphViz. The real power of SchemaCrawler is that
- you can use regular expressions to limit the tables and columns in the diagram, making it really useful when exploring an unfamiliar database
- you can discover relationships between tables that are not expressed as foreign keys, using the "weak associations" feature
Just download SchemaCrawler, place it somewhere in your java path and run it as follows:
java -classpath $(echo ../../_schemacrawler/lib/*.jar | tr ' ' ':') schemacrawler.Main -server=mysql -database=your_database_in_localhost -user=your_user -password=your_password -infolevel=maximum -command=graph -outputformat=pdf -outputfile=my_database_diagram.pdf $*
Notice also that you can choose different file formats like
- png
- jpg
- dot
- svg
and many others.
You may also decide whether foreign-key names, column ordinal numbers, and schema names should be displayed by setting the following properties in the SchemaCrawler configuration file, schemacrawler.config.properties.
- schemacrawler.format.show_ordinal_numbers=true
- schemacrawler.format.hide_foreignkey_names=true
- schemacrawler.format.show_unqualified_names=true
There's more info and examples at http://schemacrawler.sourceforge.net/diagramming.html