Database Visualization

Keeping documentation up-to-date is always a pain in the ass. I was recently working on updating our database diagrams and thought that there may be a way to automate the process.

I found 2 solutions:

1. XMI
2. RailRoad

For solution (1), it worked, but I had to tweak it slightly. Because we are using PostgreSQL, I had a problem when UmlDumper tried to execute current_database for the adapter. This works fine for the MySQL adapter, but no such function exists for the PostgreSQL adapter. I quickly hacked it and just replaced it with a general name for our database, as we didn't have to be dead-on here.

I was quite disappointed when Dia did not know how to open up the XMI file. So, searching around, I found a program called Umbrello. It works great. Unfortunately, I had all my UML classes, but no diagram! I had to drag and drop each one individually (I couldn't figure out how to drag them all at the same time T.T). I then manually added in all the lines and made it pretty. Not too bad, but it was partly automated and partly manual.

For solution (2), it worked great. I used a rake task to generate the svg files. I had several issues with this, which was the fact that RailRoad is used to diagram models in RoR, not the database. Hence, I ended up with a lot of relations that didn't exist in the database. This meant TONS of lines. The lines are also quite hard to follow as they constantly overlap and cross each other.

2 major issues I had was that there is a 1-to-1 relationship (for efficiency purposes). However, the diagram did not reflect this. For example, we have class A and B that are in a 1-to-1 relationship. A will have has_many relationships and so will B. However, the diagram showed that all of B's has_many relationships were related to A, not B. I couldn't figure out why.

The other issue was a logged bug. If the model ends in an 's', the diagram gets messed up. The diagram will automatically drop the trailing 's'. This created incorrect relationships.

For these 2 major issues, I was unable to use solution (2) in the end. However, if more improvements are made, I may give it another chance as the whole process was completely automated. If your interested, it also does Controller diagrams.

If anyone else has a better way of producing automated diagrams of their database or models, give me a shout. I'd love to hear other solutions.


W