Database modeling conclusions

Well, this morning I've reached a conclusion to my search for database modeling happiness.

First I would like to point out that a tool exists to examine a PostgreSQL database and output a file that can be read by Dia, and that it does actually work. It requires that relationships between tables be defined in the database (as foreign keys), and does not seem to generate very appealing output at first glance, but it did actually get my existing database model into Dia without much hassle. The utility is PostgreSQL Autodoc; I used the Debian package.

Also - Power*Architect does work. When I first tried using it, it tossed up a cryptic exception because the filesystem on my database server was full. Oops. After I grew my filesystem and got the program connected, it drew up a diagram of my database in more or less the same way as Dia - not particularly good-looking, with the same relationships based on the same foreign keys. It did one thing better, though, which is that it spit out a one-page PDF image of my database upon request, which then printed up nicely without complaint. I will likely be using it for this purpose from now on.

At this point, it was obvious to me that neither of these tools were going to allow me to extend or design tables any better or easier than simply editing the migration, so that's what I am going ahead with now. It really just comes down to the fact that I would rather write Ruby than SQL or some kind of pseudo-code. However, I am pleased with my experiment, as I now have a nice printed diagram to refer to throughout the process and I can update it easily as I continue my work.

Also, I haven't gotten the impression that there are any other tools (free or otherwise) which would perform the functions I am looking for any better than the two I have been trying out. However, it's always possible that I am just not searching in the right places. I'll certainly be keeping an eye out for this type of software in the future.

Until next time...


Database modeling tools and me

I am in the process of developing an application to keep track of product testing at my company, Storix Software. Since the whole thing was my idea, I have been given nearly absolute creative and technical freedom on the project, so I am getting back in touch with my old favorite development framework. You know it, you love it: Ruby on Rails.

In addition to being a nice excuse for me to use TextMate on my new 15" MacBook Pro (mmm, kool-aid), this is putting me back in touch with the wonderful world of web development, and giving me an opportunity to cover some new ground. Rails is as slick as ever; catching up with a year and a half of framework development has consisted primarily of removing plugins!

This morning I had the opportunity to demonstrate the initial prototype of my application to the rest of the development team, and I was happily rewarded with a great deal of feedback, generally centered around increasing the complexity of the data structure to provide a more rigid definition of our testing methodology.

So, that's all well and good, but now I need to perform a fairly serious restructuring of my database model. Historically, my approach to this problem has been to just simply write out Rails migrations by hand. It works very well for deployment, and it is very likely that my finished model will be expressed as a migration, but I would like a little bit more flexibility while I play around with a new model, something beyond what I can accomplish with pen and paper or with a text editor. I would also like to be able to output pretty little printable graphs that I can pass out to the rest of the team, so they can quickly and easily grok all the different aspects of the application.

I think I need a data modeling tool. Of course, not just any data modeling tool will suffice. It must be open source; I don't have $3,999 for this small luxury and I simply do not agree to any kind of restrictive license terms at all. Shareware can die in a fire. With my requirements in mind, where do I begin?

The first likely candidate that crosses my path is a program called Dia. I've heard the name before and I can install the thing with an apt-get - so far, so good. It opens quickly when I run it, and presents me with what looks like a blank piece of graph paper and a little toolbox full of shapes. I start clicking around, find some kind of options which appear to be oriented toward modeling databases, and then promptly realize that I have no interest whatsoever in completely re-entering my existing database design.

Thus, my requirements are reborn: I need an open-source database modeling tool which can import models from existing databases, quite preferably from my local PostgreSQL server.

Next. Further investigation uncovers a program called Power*Architect: "SQL Power's Power*Architect is a cross-platform, open-source data modeling tool with a GUI and an embeddable API." Sounds great. It's a Java application and it has an OS X installer, so I throw it at my MacBook. It installs, opens, accepts my database connection information, and explodes in a miserable little ball of unhandled exception stack trace when told to connect. Great.

I make a note to file a bug with the project, but I still need my data modeled, and I need it modeled now. Am I looking for a program that doesn't exist? The search continues...