Data Architecture and Database Design Made Easy With ER/Studio

Supposing everything is fine now that you are finished with your logical database design, you will generate the physical model. This functionality of ER/Studio is, again, wizard driven and very intuitive to use.

Figure 7: Generate Physical Model wizard, page 1.

ER/Studio 7.1 supports Microsoft SQL Server 2005. It supports all SQL Server versions down to 6.5. As you can see from the screen shot above, this is quite a mighty wizard. On eight pages you can define, for example, what entities should be created or what type of index (clustered or nonclustered) should be created for the primary keys.

Figure 8: Generate Physical Model wizard, page 2.

On the second page of the wizard, you'll also notice a very useful option. The wizard is able to generate a physical index on a foreign key. This is something that is not done automatically, for example, when you create a foreign key with the SQL Server Management Studio. Missing indexes on such foreign keys are typical errors that lead to performance problems. But the wizard let's you specify even more options like storage parameters for tables and indexes and fill factors. You can decide on a naming convention — uppercase or lowercase — and whether invalid SQL characters should be replaced automatically.

Another feature of ER/Studio comes in very handy at this point. Once you've decided on all the options and parameters in the Generate Physical Model wizard, you can save the settings for future use so you don't have to specify everything again the next time you use the wizard.

Once the physical model is generated, you can work with it just like the logical model. At this point, there is not a single DDL statement generated yet. If you wish to do so, you might want to check the physical model (just like you did with the logical model). This process is also wizard driven and, again, you can set multiple parameters and options to be checked, like checking for tables without primary keys, checking for keyword violations and invalid characters, and checking the length of names. Remarkable at this point are the options to check for tables that don't have any index at all and for tables that have an index count exceeding a certain threshold.

Figure 9: Validate Physical Model wizard, page 3.

These are very useful options to deal with problems before they even arise. A table without any index at all is likely to suffer from poor performance. Just like a table with too many (probably redundant) indexes is also a good candidate for poor performance.