10

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).

Improve this question
1
  • 1
    Is anyone aware of a tool that arranges the diagram via a weighted graph or similar algorithm to reduce overlaps?
    – Chris Nava
    Nov 17, 2009 at 18:18

5 Answers 5

Reset to default
2

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.

Improve this answer
2
  • 1
    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...
    – user18352
    Nov 17, 2009 at 17:57
  • 1
    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/…
    – DaveParillo
    Nov 17, 2009 at 18:22
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

Improve this answer
2

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

enter image description here

Improve this answer
1
  • This did the trick for me :)
    – Developerium
    Oct 25, 2020 at 19:58
0

probably depends on which database you're using. I've tried this with Microsoft SQL Server Management Studio and it creates and lays out the diagram. I can't really say if it meets your requirements but the express edition is free so it's always possible to try it out.

Improve this answer
1
  • 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
    Nov 17, 2009 at 17:40
0

I've used Druid for this in the past and been very satisfied. It's open source/free and you can move the objects in the diagram around. The UI can take a fair bit of getting used to, though.

Improve this answer
5
  • From the description, it doesn't sound like Druid does automatic layout.
    – user18352
    Nov 17, 2009 at 17:42
  • How are you planning to load the table information into the program?
    – Pops
    Nov 17, 2009 at 17:48
  • 1
    Reverse engineer from an existing database.
    – user18352
    Nov 17, 2009 at 17:52
  • 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."
    – Pops
    Nov 17, 2009 at 18:14
  • 1
    Heh sorry. By reverse engineer, I meant having the program log into the database to retrieve the table structures and constraints.
    – user18352
    Nov 17, 2009 at 18:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .