Tool for automatically generating and laying out an entity relationship diagram?

Question

I've seen a lot of tools which can reverse engineer an ERD from an existing database, but I haven't been able to find one which is capable of automatically laying out the diagram in a reasonable way. Most of them just plop all the entities down on top of each other and call it a day. Some make an attempt at organizing the entities, but they don't do a very good job of it.

Is there any tool out there that will reverse engineer the structure of an existing database, and then automatically lay it out in a way which is easy to understand and reveals the organization of the database? If I were to make such a tool, I'd have it minimize the length of lines connecting entities, minimize the number of lines which cross each other, and make groups of related entities stand out from each other. I'd also try to deduce which tables are lookup tables, which ones are mana-to-many intermediate tables, etc and lay out the entities such that these roles are obvious to a person looking at the diagram.

I don't exactly have the funding to make the above, but I do have some funding to buy a tool like that if it's good.

Edit: I should mention that I'm trying to diagram a database with 100+ tables, so I'd like to automate as much of it as possible. The database is not one I'm very familiar with, so I'm looking to learn from looking at the diagram rather than dumping what I know into a diagram (which seems to be what most diagramming tools are designed for).

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

1. you can use regular expressions to limit the tables and columns in the diagram, making it really useful when exploring an unfamiliar database
2. 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

Solution 3

DBeaver has the ability to view database structure as an entity relation diagram.

Comments

[Admin]

Is anyone aware of a tool that arranges the diagram via a weighted graph or similar algorithm to reduce overlaps?

[user18352]

The database diagram created by management studio is probably the best I've seen, but it still doesn't handle 100+ tables very well. It ends up being a jumbled mess.

[user18352]

From the description, it doesn't sound like Druid does automatic layout.

[bhaskar]

How are you planning to load the table information into the program?

[user18352]

Reverse engineer from an existing database.

[user18352]

I've tried viso too. Maybe my problem is that the relationships in the database are actually a jumbled mess and is being accurately represented by the jumbled mess I get...

[bhaskar]

Yes, I saw that in your original question, but how? Importing an Access file? Telling the program the login information to the database? It's not clear what you mean by "reverse engineer."

[DaveParillo]

I also have used visio to do this (on ms access files only) The process is simple enough, but if your source database is a mess, as you say, I'm not sure any software is going to automatically sort this out for you perfectly. The good part about visio, is that you can modify the ER diagram and reproduce the database using VBA: eggheadcafe.com/software/aspnet/32121825/…

[user18352]

Heh sorry. By reverse engineer, I meant having the program log into the database to retrieve the table structures and constraints.

[Developerium]

This did the trick for me :)