Lesson 7 — Physical Design, Tools, Patterns, Naming, and PRISM
Physical design adapts logical meaning to a real technology
A sound PDM does not merely translate entity names into table names. It resolves logical abstractions and balances implementation constraints without losing the business rules the logical model established.
Typical physical decisions include: - subtype implementation; - physical datatypes and constraints; - reference-data representation; - surrogate keys while preserving natural uniqueness; - indexes; - vertical/horizontal partitions; - views/materialized views; - justified denormalization.
These choices are requirement-driven, not habits.
Naming: logical readability vs physical constraints
Logical naming
Favor meaningful, business-friendly full words where possible. The audience needs to understand the business meaning without decoding technical shorthand.
Physical naming
Adapt to DBMS constraints and approved conventions. Abbreviations and underscores may be appropriate if traceability to the logical name remains clear.
A logical CUST_NM and a physical Customer Name with spaces is a sign the naming conventions may have been reversed. Logical Customer Name mapped to approved physical CUST_NM is source-aligned.
PRISM
Chapter 5’s database-design mnemonic:
- P — Performance and ease of use
- R — Reusability
- I — Integrity
- S — Security
- M — Maintainability
PRISM prevents physical tuning from collapsing into a single objective such as “make the query faster.” A design improvement on one dimension can damage another.
Example: denormalization may improve read performance but reduce integrity/maintainability unless synchronization is tightly controlled.
Tools support judgment; they do not replace it
Modeling tools
Create/manage model structures and often support forward/reverse engineering.
Lineage tools
Trace source-to-target relationships and transformations. Lineage is also a modeling deliverable because it connects represented requirements to the real implementation and supports impact analysis.
Profiling tools
Examine actual data values and patterns. Useful when the documented model and reality may disagree.
Metadata repositories
Preserve shared definitions, model content, ownership/context, and related knowledge so the model is not trapped on an individual laptop.
Patterns vs industry models
Data model pattern
A reusable generic structural solution for a recurring situation — for example, a reusable Party–Role structure.
Industry data model
A broad pre-built reference model for an industry. It can accelerate discovery and expose missing concepts, but it must be customized to the organization’s requirements, vocabulary, rules, and scope.
Exam trap: “we bought an industry model, so we can skip requirements workshops” is wrong. The opposite extreme — rejecting all reference models — is also weaker. Use them as starting knowledge, then validate and customize.
Source-target mapping as a model deliverable
Suppose an analytics attribute exists in the model but nobody knows which source field supplies it. The model is not fully validated. Source-target mapping/lineage should identify the origin and mapping rule before the attribute is treated as grounded.
If the source is known but stakeholders disagree what the attribute means, the issue shifts from lineage to business definition/stewardship.
Source anchors: current Mastery Lab tools, patterns, naming, and physical-design sections, pp. 154–160.