Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Tool Review (Free): DBDesignerFork

Last updated Mar 28, 2003.

I’m always impressed with people who write software for free. They see a need, and they fill that need for themselves, and then donate the code to the community. I like that, and I’ve done it myself from time to time.

I’m also a big fan of database design tools. I need an ERD tool — I use it to communicate a design to the development team, the Business Analysts on the organization side, and the Data Professionals on my own team. It’s a simple, quick way to ensure that I have the requirements right, and it helps the development team work with my design quickly and easily.

I have a personal issue with many of the Entity Relationship Design tools out there, from just about everyone. The Database Designer within SQL Server Management Studio (SSMS) has two major issues: It’s a physical model (meaning that as you design you’re actually creating tables, before you even know if the design is right) and it’s completely non-standard in the notation. I’ll explain why those are important in a moment.

The other commercial offerings aren’t much better — some of the well-known, larger products are hideously expensive. I don’t know anyone who creates that many new designs that would justify more than a couple of hundreds of dollars for a tool like this. And some are so complicated, it would take weeks to learn to use them properly. That doesn’t make much sense — there are only a few primitive forms in an ERD, and generating the code they create just isn’t that complicated. Still others do more than ERD’s, so of course they are larger than needed and don’t focus well enough on the ERD.

So I researched the web, and found a tool called DBDesigner. It seems to have everything I need, and it’s free! But — it’s designed for MySQL only. I have no problem with that, but of course I work on SQL Server. Then I found that there was a “fork” of the software, which means that the original software was copied, and then changed to a new set of features. The product that forked off of the DBDesigner tool was renamed to, oddly enough, DBDesignerFork. You can find that product here: http://sourceforge.net/projects/dbdesigner-fork/

So I thought I would take you along on my experiences with the product to see if it fits your needs. I’ll take a set of tasks that I want to accomplish and tell you how I did with the product, what I learned, and whether or not I plan to keep and use it.

Installation and Documentation

One of the best surprises for this product was the installation — there wasn’t any! When you hit the link for the project, you’ll find a set of software packages on the files area. One is for Linux, which I have not tried, and the other one was a simple ZIP file. Open that, copy it somewhere, and you’re in business. It’s pretty small as well — on my system, it ended up being around 15 Megabytes.

All that is needed to launch the product is to double-click the DBDesignerFork.EXE file. If you want to have a permanent link to it, just right-click that file and then paste it as a Shortcut wherever you want.

Once you launch the product, you’re dropped into the main panel and a “tip of the day.” Right away, you’ll notice that the product doesn’t follow the Microsoft Windows Graphical User Interface (GUI) standards strictly, but it’s not too jarring and it’s completely understandable in a multi-platform software package.

There are the typical menus on the top bar, a tool bar on the left, and “palettes” on the right-hand side, in the fashion of properties panels.

I do try and glance over the manuals when I get a package, so the first thing I did was to open the “Help” menu and select the documentation, which is online. That has plusses and minuses, but it’s a pretty comprehensive set of documentation, especially for an open-source product.

By reading the documentation, I found that there are four basic things to do in the model: Lay out the tables, define the relationships, and then optionally add notes and graphics to the display. There’s also an interesting Query Designer mode, where you can specify the relationships and so on within the tool. I find that SQL Server Management Studio has a great coding interface, especially in SQL Server 2008 and higher, so I didn’t evaluate that feature in this product.

Reverse-Engineering a Database

After I reviewed the documentation, I decided to create a model from an existing database. This is normally how I evaluate an ERD tool, since I already know what the outcome should look like. I’ll use the pubs sample database, primarily because it is small and well understood.

To begin, I had to create a database connection. This took a moment or two, since it doesn’t work like the screen tends to indicate it will — it seems like you should just fill out the name and connection information on this panel:

But in fact, you have to click the “New Database Connection” button at the bottom and fill out this panel:

Which is pretty straightforward. It does have the MSSQL (Microsoft SQL Server) type of connection, along with MySQL, Oracle, and even ODBC, which is very nice. I entered the information here for SQL Server security, since I am crossing domains for this example. It saves the connection information, but not the password, which is acceptable in my opinion. I would rather the developers not try to spend time trying to figure out how to properly encrypt my credentials — I’ll just enter it each time. There is also an “Advanced” tab here that allows you to enter more fine-grained information for the connection.

Each connection is also database-specific, from what I can tell. That means if you have several hundred databases you’re designing, you would have a connection per database. That might cause a visual problem later, but for now I’m OK with this.

Once I’ve connected (there’s only an error screen, nothing to tell you it worked other than a small set of text in the bottom right hand corner), I selected Database | Reverse Engineering. I was once again prompted for my credentials, which seemed a little strange, but after I selected those again I received the following screen:

Although the fonts were a little messed up, I de-selected a couple of system tables (Microsoft’s fault — they aren’t marked as such) and changed the number of tables to show at a glance and I also de-selected the option to have the tool substitute Microsoft SQL Server data types for more ANSI compliant ones. After I selected Execute, I got this diagram:

Right away I didn’t like the format. I don’t think this is standard notation at all, but since I had read the manual, I realized I could change that using the Display | Notation menu. It doesn’t have the proper names, but “Crows Foot” was the closest to what I was used to. After that change, and with a little re-arranging of the tables I got a decent-looking diagram.

I would like to be able to “tie” the lines to the actual columns that are keys to each other, but the relationship labels also serve that purpose.

I got a rather nasty surprise when I tried to save the model. I use larger fonts on my laptop, and the save dialog is apparently coded in such a way that I can’t see the “OK” button. So until I changed my fonts, I couldn’t save anything! There is also a “save in database” mode, but I couldn’t make it work. I think that part is designed for a MySQL system.

Creating a Model

Creating the model was a simple matter of following the samples in the documentation, clicking twice on the tool icon and then on the main screen. Right-clicking any object brought up a properties panel, and worked as expected. The menus at the top of the screen do tend to space themselves in an odd way from time to time, something that I think once again comes as an artifact of being multi-platform.

Implementing and Scripting the Model

Using the “pubs” example from the previous steps, I clicked on the File | Export | SQL Create Script, and I got an option to save it to clipboard or to a file. I saved it to clipboard, and in this simple example, it worked fine.

Verdict

The key advantage to this product is that it is a mix of the Logical and Physical design models. You can create a model with no database link of any kind and no strict language constructs, which makes it Logical, but you can also tie the design to a script or even enforce the changes you make, making it Physical. Personally, this is the route I like to go anyway.

There are rough edges — the look and feel isn’t Microsoft quality, and the fonts thing was a big deal for me. Also, I would like to see standard notation, such as Chen or the IDEFX formats, but those are small nits. The point is that I could get the design across without a lot of explanation to the developers or BAs.

I think I’ll keep this one around for a while. I’ll update this article if and when the product changes significantly.

InformIT Articles and Sample Chapters

I’ve covered ERDs in general in Database Objects: Entity Relationship Diagrams.

Books and eBooks

You can find a full treatment on database design in Database Design for Mere Mortals®: A Hands-On Guide to Relational Database Design, 2nd Edition.

Online Resources

You can read a little more about proper design here.