Physical Models

This section contains the following topics:

Objective

The objective of a physical model is to provide a database administrator with sufficient information to create an efficient physical database. The physical model also provides a context for the definition and recording (in the data dictionary) of the data elements that form the database, and assists the application team in choosing a physical structure for the programs that will access the data. To ensure that all information system needs are met, physical models are often developed jointly by a team representing the data administration, database administration, and application development areas.

When it is appropriate for the development effort, the model can also provide the basis for comparing the physical database design against the original business information requirements to:

Demonstrate that the physical database design adequately supports those requirements.
Document physical design choices and their implications, such as what is satisfied, and what is not.
Identify database extensibility capabilities and constraints.

Support for the Roles of the Physical Model

Support is provided for both roles of a physical model:

Generating the physical database
Documenting physical design against the business requirements

For example, in a logical/physical model, you can create a physical model from an ERD, key-based, or fully attributed model simply by changing the view of the model from Logical Model to Physical Model. Each option in the logical model has a corresponding option in the physical model. Therefore, each entity becomes a relational table, attributes become columns, and keys become indices.

Once the physical model is created, you can generate all model objects in the correct syntax for the selected target server directly to the catalog of the target server, or indirectly as a schema DDL script file.

Summary of Logical and Physical Model Components

The following table summarizes the relationship between objects in a logical and a physical model:

Logical Model	Physical Model
Entity	Table
Dependent entity	Foreign Key is part of the child table's Primary Key
Independent entity	Parent table or, if it is a child table, Foreign Key is NOT part of the child table's Primary Key
Attribute	Column
Logical datatype (text, number, datetime, blob)	Physical datatype (valid example varies depending on the target server selected)
Domain (logical)	Domain (physical)
Primary key	Primary key, Primary Key Index
Foreign key	Foreign key, Foreign Key Index
Alternate key (AK)	Alternate Key Index-a unique, non-primary index
Inversion entry (IE)	Inversion entry Index-a non-unique index created to search table information by a non-unique value, such as customer last name.
Key group	Index
Business rule	Trigger or stored procedure
Validation rule	Constraint
Relationship	Relationship implemented using Foreign Keys
Identifying relationship	Foreign Key is part of the child table's Primary Key (above the line)
Non-identifying relationship	Foreign Key is NOT part of the child table's Primary Key (below the line)
Subtype relationship	Denormalized tables
Many-to-many relationship	Associative table
Referential Integrity relationship (Cascade, Restrict, Set Null, Set Default)	INSERT, UPDATE, and DELETE Triggers
Cardinality relationship	INSERT, UPDATE, and DELETE Triggers
N/A	View or view relationship
N/A	Prescript or postscript

Referential integrity is a part of the logical model, since the decision about how to maintain a relationship is a business decision. Referential integrity is also a physical model component, since triggers or declarative statements appear in the schema. Referential integrity is supported as a part of both the logical and physical models.

Denormalization

You can also denormalize the structure of the logical model, or allow data redundancy in a table to improve query performance so that you can build a related physical model that is designed effectively for the target RDBMS. Features supporting denormalization include:

Logical only properties for entities, attributes, key groups, and domains. You can mark any item in the logical model logical only so that it appears in the logical model, but does not appear in the physical model. For example, you can use the logical only settings to denormalize subtype relationships or support partial key migration in the physical model.
Physical only properties for tables, columns, indexes, and domains. You can mark any item in the physical model physical only so that it appears in the physical model only. This setting also supports denormalization of the physical model since it enables the modeler to include tables, columns, and indexes in the physical model that directly support physical implementation requirements.
Resolution of many-to-many relationships in a physical model. Support for resolving many-to-many relationships is provided in both the logical and physical models. If you resolve the many-to-many relationship in the logical model, the associative entity is created and lets you add additional attributes. If you choose to keep the many-to-many relationship in the logical model, you can still resolve the relationship in the physical model. The link is maintained between the original logical design and the new physical design, so the origin of the associative table is documented in the model.

Email CA about this topic