how to generate ERD or UML for a database?

Question

Referencing a previous question, it doesn't seem possible to easily autogenerate a UML or ERD graph. How can this be done? Even the the detail which describe fudforum.*; provides would do the trick, except that you can't use a wildcard.

Something like mysqldump -d -u <username> -p<password> -h <hostname> <dbname> but more readable?

It looks like devart doesn't run on Linux, but I'm looking into that.

mysql:

mysql> 
mysql> describe fudforum.fud30_xmlagg;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| name           | varchar(255) | NO   |     |         |                |
| url            | varchar(255) | NO   |     |         |                |
| forum_id       | int(11)      | NO   | MUL | 0       |                |
| xmlagg_opt     | int(11)      | NO   |     | 2       |                |
| last_load_date | bigint(20)   | NO   |     | 0       |                |
| custom_sig     | text         | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
7 rows in set (0.04 sec)

mysql> 
mysql> quit;
Bye
thufir@dur:~$ 

Solution 1

mysql workbench can reverse engineer and create erd's

http://www.mysql.com/products/workbench/

Solution 2

I've tried many times to get MySQL Workbench to auto generate an ERD with relationship lines but always left frustrated. Most of the databases I was working with used MyISAM tables without defined foreign keys. That seemed to prevent Workbench from generating the relationships I wanted or I just couldn't understand how to make it work. I tried many other solution as well but again never found the easy solution I was looking for until I stumbled on this blog post at mysqlworkbench.org.

MySQL Workbench Plugin: Auto-Create Foreign Keys

The post is a full explanation on how to get Workbench to search all of your tables for candidate foreign keys in other tables. It even shows how to get a working GUI for it. The best part is that the article links to a Python script that can be installed in Workbench as a plugin so you it's all handled for you.

Once the plugin is installed you run it and give it a pattern to use for testing whether keys match. It then gives you a list of the keys it thinks match which you can select if you agree. Then you click a button and it generates the ERD for you with all the relationship lines in place. Hallelujah!

Many thanks to akojima at MySQL Workbench. Now if only I could take the Delorean back four years and find this when it was published in 2010.

Solution 3

There is a tutorial how to convert Oracle 10G to UML using Eclipse and Dali plugin.

You can just swap Oracle with your database sql connector inside Dali and it would do the job.

Solution 4

mysqlshow command:

    mysqlshow fudforum

View more solutions