What is the difference between physical versus logical data modeling?

Answer 1

Logical data modeling is the exercise to document and define the relationships between data elements. Typically, it involves

# identifying entities (e.g., "customers", "orders") from the business environment # identifying how specific instances of each entity are differentiated from other instances, the logical key (e.g., "customer_id", "order_number")

# grouping together other attributes that describe the entity (e.g., "customer_address", "ship_to_address") AND which can also be uniquely determined based on the entity's key # finally, documenting the business rules (relationships) between the entities (e.g., "a customer may place one or more orders", "each order must be placed by exactly one customer") Note that logical data modeling does not consider any physical representation of how the data will be stored and it doesn't attempt to anticipate or correct any performance issues that may arise during implementation.

Tasks such as these occur during the physical data modeling phase. At this point decisions will have to be made about data storage (Oracle relational DB, VSAM files, JMS message stores, etc.). Considerations for how the data needs to be accessed, combined ("joined") and the performance characteristics of the intended deployment environment will be documented.

Taking the purely logical entities, attributes and relationships, the physical modeler makes (and documents the reasons for!!!) altering the logical model. One-to-many relationships may be "denormalized" into the "one side" of the relationship, forming a repeating group (e.g., collapsing "a customer may have multiple phone numbers" into just a "customer" entity with attributes of "home_phone", "work_phone", "mobile_phone", "fax_phone").

Decisions about where to place the data (same database? different databases on different servers?) as well as partitioning, archival, purging plans have to be done within the constraints of the business requirements.

Oddly enough, logical data modeling is more of a science and physical modeling is more of an art in that two business analysts can discuss the logical model and resolve most differences of opinion logically (so to speak) by providing real-world examples that would negate a particular representation. Physical database design is not so precise, however. The modeler must know (or anticipate) a number of things about future uses of the data and about the characteristics of the particular database management system, programming language(s), communication channels, etc. Many assumptions go into the creation of a physical model. How well that model will eventually perform depends, in large part, in the quality of those assumptions.