Developing the Physical Model
Go Up to Using ER/Studio Data Architect
In order to create a database, ensure you have the client software installed and are using the correct connection strings. For more information, see Connecting to Database Sources and Targets.
If you do not have the client connection software for the platform for which you want to create a database, you can generate a physical model from the logical model (See Generating a Physical Data Model), and then change the database platform to the target database. You can then access the Database Wizard to create the DDL required to create the database.
Generally, a logical data model represents business information and defines business rules, and a physical data model represents the physical implementation of the model in a database. The physical design addresses the technical implementation of a data model, and shows how the data is stored in the database. For example, you can specify the datatype of each column in the table, and determine how tables will be stored in the database.
To design the most effective data model possible, focus on the logical design before developing the physical design. A physical data modeler should have the technical-know-how to create data models from existing databases and to tune the data models with referential integrity, alternate keys, indexes and how to match indexes to SQL code. In systems development, the goal is to create an effective database application that can support some or all of your enterprise.
The physical design focuses on performance and tuning issues for a particular database platform. A highly normalized database design often results in performance problems, particularly with large data volumes and many concurrent users. These problems are often due to complex query patterns, the cost of multi-table joins, and the high I/O intensity of querying or modifying large tables. When addressing performance problems, you should consider the following physical design issues:
- Transforming Model Objects from Logical to Physical: How the elements of a physical model correspond to the elements of the corresponding logical model.
- Optimizing Query Performance on the Physical Model (Denormalizing the Physical Model): How to modify the logical design to increase the efficiency of database operations.
- Creating and Editing Indexes: How to create indexes to increase the efficiency of read operations (queries) without sacrificing the efficiency of write operations (insert, update, delete).
- Physical Storage Considerations: How to determine the physical storage of the tables and indexes in the database to maximize efficiency and allow for growth.
For information on physical data model objects in ER/Studio Data Architect, see the following topics:
- Creating and Editing Entities and Tables
- Creating and Editing Attributes and Columns
- Customizing Table DDL
- Creating and Editing Indexes
- Defining Table Storage
- Creating and Editing Database Dependent Objects
- Creating and Editing Collections