Data Architecture and Database Design Made Easy With ER/Studio

When everything is just right, you're finally ready to generate the DDL for the database. It's not too difficult to guess that this is another wizard-driven process with plenty of options and parameters to be set.

Figure 10: DDL Generation wizard, page 1.

You can specify options for tables, indexes, users, and roles. As there are simply too many to keep in mind all the time, you can save all your settings using ER/Studios Quick Launch feature and simply load them the next time you generate some DDL.

Figure 11: DDL Generation wizard, page 4.

Once the generation process is done, you are prompted to open the script with ER/Studio's built-in Universal ISQL editor, if needed. This tool is just like the SQL Server 2000 Query Analyzer.

Figure 12: Universal ISQL editor.

If you are familiar with Query Analyzer, you will get used to this tool very quickly. However, keep in mind that changes made here to the DDL source file are not reflected in your physical or logical model. If you notice at this point that something needs to be corrected, you should go back to your logical and physical models and make the changes there. Don't do anything else with the ISQL editor but open files and execute statements.

When you work with your model after the initial physical model has been created, both models will be out of synch. To synchronize them and bring both models up to date again, use ER/Studio's Compare and Merge Utility.

Interestingly, you can run this comparison against another model in the currently opened file or against another model in another file. This option will be very useful for enterprise data architects who work with many different models and databases and need to ensure that changes made to one model are synchronized in others as well. Of course, this can be done manually, but it certainly is a plus when the process can be automated.

Figure 13: Compare and Merge Utility, page 5.

From this screen shot, you can see that it's a two-way utility. That means you can work in either the logical or the physical model, and then use this wizard to synchronize both models again.

Another interesting feature of ER/Studio is the "Capacity Planning" feature. When designing a new database one of the important questions you try to answer in advance is how large the database is expected to grow over time. Questions like these can easily be answered with this tool.

Figure 14: Capacity Planning Options.

As you can see, you can choose the table to analyze, specify the initial amount of rows in the table, enter your growth parameters (by percentage or by rows), and the interval to be predicted. You can further decide if only the growth of the base table should be estimated or if the indexes on that table should also be included in the calculation. If you only need the raw numbers, you can see them immediately in the lower left corner in the "Sizing Estimates" frame. If you need the numbers for documentation purposes, you can generate and print a report.