SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 352 of 365 Next >

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.

Figure 1

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.

Figure 2

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:

Figure 3

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

Figure 4

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:

Figure 5

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:

Figure 6

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.

Figure 7

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.

Figure 8

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.

< Back Page 352 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address