|
|
|
|
|
Before getting into the meat of this
excellent modeling tool, we do need to discuss the logical modeling capabilities because
ER/Studio offers a level of functionality not available anywhere else. It is very
common to have constructs in a logical model that do not exist in a physical model and
vice versa. An example of a physical construct that doesn't exist in a logical model
is an intersection table to resolve a many to many relationship. The three leading
tools on the market: ER/Studio, ERWin, and PowerDesigner all have the capability to
construct both logical and physical data models. ERWin data models consist of a
single file that contains the logical and the physical data model. You can switch
from one "view" of your data model very simply via a menu option, but ERWin does
not distinguish between a logical and physical model. Every construct that exists in
one is forced to exist in the other. ERWin has a very kludgy way of
"fixing" this problem by allowing you to designate elements to only appear on a
logical model or a physical model. This accomplishes the "effect" of
having a separate physical and logical model by hiding elements, but it is an extremely
poor way of handling that functionality. PowerDesigner is even worse. It
creates a separate file for the logical and the physical model. A physical model can
be derived from a logical model, but it is a one shot, one way process. Neither
ERWin nor PowerDesigner allow you to maintain truly separate logical and physical data
models while still allowing two way synchorinzation of selected elements between the two.
ER/Studio offers a level of fucntionality well beyond what any other tool does.
The screen shot below hopefully demonstrates this well. (This was a feature
that I overlooked the first few times through until I had it pointed out to me.)
This was shot at a much higher screen resolution in order to be seen. If you look
closely at the tree view on the left, you'll notice 1 logical model and 3 physical
models. I've collapsed one of them to really demonstrate the difference.
You'll notice that the 2 physical models each contain a subset of the logical model.
These are completely separate models.
You can add elements to any of the physical models and also to the logical model and keep them local to just that model. If you choose, you can synchronize any changes in the logical model to the physical model and vice versa. During this merge process you are allowed to maintain this separation of models. In order to merge the models, you select Model | Merge Models. You are then walked through a 3 step wizard that allows you to pick and choose which elements are synchornized and which are not. This functionality allows you to maintain a single logical data model while having multiple physical implementations of that same model. This is absolutely invaluable and is unique to ER/Studio. Unless you have done a lot of modeling or some enterprise level modeling, this functionality might slip by. This functionality by itself makes ER/Studio an invaluable addition to any enterprise and just might convince me to start with logical data models. I'm sure everyone has run across third party applications that run on top of SQL Server and various other DBMSes or you work for a vendor producing these. With ER/Studio, you can create a single logical model and generate separate physical models for each platform you are going to support. You can then tweak the physical models while still maintaining a cohesive balance across your application. You might also run into a case where you are designing a system that is going to process transactions in one incarnation and perform decision support in another. The transaction database could be modeled using a conventional 3rd normal form while the DSS database could be modeled using an appropriate star or snowflake schema. You still need to maintain a cohesive view of the overall system, but you can transform the logical whole into the more targeted components. There are hundreds of other scenarios that you can come up with, some of which are probably better examples than I have given. But, it still remains that this functionality is unique to ER/Studio and by itself would probably rate a purchase in many enterprises. |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.
