Quest Software has hit the ball out of the park with its Toad Data Modeler 3 (TDM) application. Ease of use, a huge amount of functionality, and an outstanding support base all combine to make TDM a superb product. Every once in a while you get lucky enough to run across a product that makes you say, “Wow,” and that’s what I said about two minutes into running TDM.
To prepare for this product review, I read the literature about TDM on Quest’s Web site, participated in a conference call with the product manager and product architect, and brushed up on some of the finer points of data modeling. I was prepared to see one thing, and was more than pleasantly surprised to see what Quest has turned out. I’ve used and experimented with quite a few other tools, and I can only conclude that Toad Data Modeler, when it comes to data modeling, is without question the killer app in this category. Is it bug (or “featureless”) free? No, but Quest actively welcomes your feedback in identifying areas for improvement.
What Does Toad Data Modeler Do?
In short, TDM makes data modeling easy, or as Quest puts it, it provides “Powerful and Cost-effective data Modeling and Design.” How often, and to what degree, do you perform data modeling? I would venture to say that less than 2% of the time for upwards of 99% of DBAs is spent on modeling. Unless you happen to be working where a new design is going to be put in place, chances are your most recent modeling experience consisted of adding columns to tables here and there during a minor change to an existing set of applications or schemas. So, when it is time to start a full scale design effort, what are you going to use?
With TDM, designing tables and establishing relationships between them is a breeze. Editing attributes can be done via several mechanisms, and the entity-relationship diagram it provides can be displayed in multiple formats. What is the difference between IDF1X and IE? Those notational model names should look familiar to you if you took a course on database design. But if not, go into the Help section, search for Notation, and browse the richly illustrated examples.
You can expect to find many of the features found in other products, chief among the features being the ability of the tool to perform reverse engineering. The complement of reverse engineering is forward engineering, that is, will the tool generate DDL for you?
As described by Quest, TDM simplifies the process of:
- Building complex entity relationship models both logical and physical
- Synchronizing models
- Generating complex SQL/DDL create and alter scripts
- Reverse-engineering legacy databases
We’ll take a look at the last two bulleted items in a minute.
One of Quest Software’s features is its support or sponsorship of user communities. The Toad Data Modeler Community contains an extensive number of Flash movies, which illustrate TDM’s features and functionality, in addition to presenting database design concepts. Within the “TDM3 Movies” section, there are (as of this writing) 21 movies providing “how to” demonstrations on many of TDM’s capabilities. In essence, you get an extensive amount of training material for free. Watch some of the earlier movies and then jump into the tool. You’ll be up and running in no time at all. Shown below is a screen shot from the first presentation (Interface Layout), and as you can see, the viewer receives a thorough introduction.
As an initial introduction to the tool, let’s look at some essential basics: installation and reverse engineering (RE). The RE review will take an out of the box model, generate the DDL (and some DML as well), and then turn around and perform RE on the same model.
Installation is very straightforward with the steps being few. Rolling over the menu bars and clicking on them produces a pop-like sound, of which I could probably do without. The navigation is very clear until the Contact (final) window, which has the Next button enabled, where upon clicking it, nothing happens. TDM beats Oracle Universal Installer in the sense that when you click Exit, you get to exit right away and not be asked if you are sure.
The system requirements are fairly minimal, as shown below.
Assuming you are an Oracle user, keep in mind that although the supported operating system shows Windows XP and Vista, you still need the correct version of either to run Oracle in a certified configuration. That would be XP Professional, and for Vista, I don’t care because I’m never going to use it, but I do know that only a few of the four hundred or however many versions of Vista are certified.
You also get a choice of RDBMSs to enable. You can choose the systems you expect to use and de-select those you do not. For example, I chose Oracle 10g and SQL Server 2005 and skipped MySQL, IBM and the rest.
Right out of the box, TDM presents you with a video rental application/design. If you want to use it inside a database, generating the DDL script can be done in two steps (F9 and click Generate, accepting the default location/file name and settings), and right away, you have something else to experiment with other than Oracle’s sample schemas. The same would hold true for your application once designed and ready to be scripted. The video rental design defaults to the SCOTT schema.
If you deselect “Generate User/Schema to Objects,” then the script is generic (no owner/schema name qualifier is used), but the comments still have SCOTT in them.
Even though the model validates (another feature of TDM), the generated script is likely to have the following errors:
High bit characters
The comments at the top produced the following -
SP2-0042: unknown command "ï»¿/*" - rest of line ignored.
- so deleting the comments altogether gets past the SQL noise.
Mismatch in object names
- Change the “typ” in the body to type, or change type in the create type statements to use typ
- Change price_per_one_day to price_per_day
The date format is dot-notation, as in 6.25.2008, not as in a typical NLS_DATE_FORMAT picture (25-JUN-2008). Issue an alter session for the dates, or edit the script.
And don’t forget a commit at the end. As a caution, the script generation will overwrite an existing file without warning. This could be important to someone in an environment where source control is not in use, so take care to source or protect an existing script.
After successfully running the generated script, I turned around and reverse engineered the schema. This is the model TDM generated (including some manual layout on my part).
It precisely matches the out of the box workspace (layout). The reverse engineering wizard is much easier to use than what you see in Visio. The connection source picks up on existing Oracle Net Services information on your computer, so say goodbye to the hard to use ODBC data source setup found elsewhere.
Who is going to benefit from using Toad Data Modeler? In other words, who is the target user audience? That audience would be database users who (per a datasheet from Quest):
- Need an easy-to-learn product that doesn’t require training
- Need an affordable product or want to extend their existing modeling capabilities
- Want to manage and document changes to an existing data model over time
- Want to synchronize the data model with the database on an ongoing basis
- Want to migrate data structures from one database version or platform to another
Okay, now the bad part. You’re thinking, “Great, another whiz bang tool I’m never going to use because it costs an arm and a leg.” You would be wrong in that thought. Toad Data Modeler is listed (shop online) for $479. Even without a volume discount, that is still a very reasonable price when compared to similar products.
Download TDM and try it out. You’re going to like it. In a follow up article for this review, I’ll go over some of the more advanced features.