Printing an ER Diagram for mySQL database (800+ tables)

Question

We have a system built by Parallels, which is relying on a huge (800+) tables to maintain everything.

I need to learn this system, in order to be able to write queries to retrieve data for report generation on various needs.

I am obviously, having difficulties isolating which tables are currently relevant for the task at hand, so I thought the best way would be, to generate and print ERD over multiple pages, for the entire system of tables.

I have attempted to drag all the tables using TOAD - which crashed :) On second attempt, I dragged tables A-N, after a (long) while, M-Z tables successfully.

I even managed to have them all resized, arranged and saved the ERD into file.

However, when I go into print or preview, the sub-process for print crashes hehe.

Any suggestions on how to print this massive ERD? or perhaps another method? The table names dont seem self explanatory, so I cant (and honestly, not really wanting) go over 800+ tables, and hope I dont miss what I need, or parts of.

I would greatly appreciate any advices or ideas on how to proceed, before I even get to actually writing the scripts and code.

The database is on mySQL under CentOS, some tables are InnoDB, some are MyISAM. Many tables seem to be having Foreign Keys.

Thanks!

Answer 1

have you tried mysql workbench? if you don't mind windows, you could try Enterprise Architect as well

Answer 2

MySQL Workbench has some great tools for reverse engineering from the create script. I haven't used it for such large databases, but you should check it.

Link: http://wb.mysql.com/

Answer 3

I worked at a place that had several hundred tables (near 1k) and no one really knew what was going on in the system, company was growing and hiring a lot. A guy was tasked with doing a diagram, and he auto-magically created a gigantic tiled poster that contained every table with lines connecting various tables (going all over the place). I'm not sure what he used, it was Unix and Oracle years ago (way before Linux and open source). There was no real rhyme or reason to the layout of the the tables in his diagram. He had successfully created a diagram of every table. The "poster" was put on a wall in a common area, and got a few looks, but no one ever really used it, it was unusable, too cluttered, too unorganized. As a result, I used MS-Word to create a single page diagram containing the 20 main tables (it went through a few iterations as I "discovered" new main tables) with lines for each foreign key and each table located in a logical manner. I showed the column name, data type, nullability, PK, and all FKs. I put my diagram up on my wall by my monitor. Eventually everyone wanted a copy of my diagram, including the person that made the "poster". When I left that job they were still giving my diagram to new hires.

I recommend that you work like an explorer, find the key tables and map them as you go, making as many specific diagrams as necessary as you discover the system. Trying to make a gigantic "poster" automatically will not work very well.

Answer 4

IIRC, MS Sql Server has some nice utility for making diagrams, I know it helped a lot, you could add a table and it would automatically add all related tables. If you could convert your tables to a MS SQL compatible sql script, this might help.