Physical Views is a pattern that shows how to encapsulate a physical database so that it can be easily accessed and optimized without affecting upper layers of software.
You have decided to use the Relational Database Access Layer. You use Hierarchical Views as interface to the application kernel and you have chosen, not to incorporate database access into the ConreteViews.
How do you provide an easy-to-use interface to your physical database tables?
Simplicity versus Performance: To achieve good performance you have to optimize your physical table layout using Denormalization, Controlled Redundancy, or Overflow Tables. However, unshielded use of these techniques messes up code dealing with physical data structures and makes database access complex. Especially Overflow Tables result in intricate code. Despite of these complex optimizations you want to have an easy-to-use interface and maintainable classes.
Flexibility: Most databases offer you the choice to use either static or dynamic SQL. Because the database pre-compiles and pre-optimizes static SQL queries, it often reduces server load and yields better performance. Some database administrators allow only static SQL on their servers. A standard reason for such a policy is database security as most users do not use the built in protection mechanisms of relational databases for reasons of effort. On the other hand dynamic SQL is more flexible and easily adapts to changes in the database scheme. It is easier to use during development. To satisfy high performance requirements, you may even want to use a low level database API. Higher levels of the access layer should not be aware of these considerations.
Encapsulate every table and every view with a ConcretePhysicalView. Use these classes to encapsulate Overflow Tables and other database optimization techniques. To provide a uniform interface derive ConcretePhysicalViews from PhysicalViews.
ConcretePhysicalView use SQL-statements to store their instance data. The main difference compared to Hierarchical Views (alias ConcreteViews) is that they shield a single physical table or view instead of multiple physical structures.
Figure 7 shows the DAG-definitions of two Physical Views that we need for our invoice example. They correspond to the physical database structure but resolve the Overflow Table (see Figure 2). To simplify the OrderPhysicalView, it should grant update access only to the Order and OrderItem data, but not to the article information. There are other Physical Views to change the Article table.
Figure 7 : DAG definitions for two ConcretePhysicalViews. Note that the OrderPhysicalView encapsulates the Order table with its overflow table OrderItemOverflow, while CustomerPhysicalView encapsulates the Customer table alone. See Figure 2 for the physical table structure.
Simplicity: Physical Views hide the complexity of optimizations and database programming. Because they have no other responsibilities, they are easy to implement. Still, the extra layer adds additional classes. If you plan to omit the Query Broker for hard-wired connections to the ConcreteViews, you should consider carefully, whether it is easier to add the layer or whether the ConcreteViews should do the database access themselves. The latter results in less classes but also less flexibility. Thus the layer of Logical Views degenerates to a layer of Physical Views.
Flexibility: Since Physical Views encapsulate database code, it is their choice, what API they use to access the database. You may have separate sets of classes using different database APIs. If you want to experiment with different access techniques during runtime, you may even use a Bridge [GOF95] and switch access modes on the fly.
Encapsulation: Physical Views enable you to optimize the physical database structure without affecting upper layers. This simplifies tuning and results in better performance. The penalty of an additional level of indirection is negligible.
Mass problems: It is easy to design a generator that builds first-cut versions of ConcretePhysicalViews. As long as you use no Overflow Tables, you just have to wrap the corresponding SQL statement. More sophisticated generators may also handle Overflow Tables.
What to encapsulate? Each ConcretePhysicalView should encapsulate a group of read/write/update/delete SQL statements on a physical table and its corresponding Overflow Table. Since Hierarchical Views refer to more than one ConcretePhysicalView, you also have the choice whether to join two tables on the database using SQL, or whether to join them in the access layer. A good point to start is to define a ConcretePhysicalView for every root table such as Customer and Article. Furthermore build one ConcretePhysicalView for every compound entity, you have defined database views for, such as the Order/OrderItem relation. If you use a Query Broker you may analyze its decisions to find further candidates.
Encapsulating read-only views: To keep the Physical Views as simple as possible you should consider, which ConcretePhysicalViews have the right to update the data they have read. Physical Views represent database views and most databases do not support writing to views. Hence, if multiple tables are involved, a Physical View with read-only access is simpler than one with read-and-write access. A good idea is to start with exactly one Physical View having write access to a certain table.
Programming Tools: ConcretePhysicalViews are generic. Use a generator or macro technique to implement them. You may also consider templates.
Use of stored procedures and other APIs: Most databases offer stored procedures to do computation on the database server. Since Physical Views work directly on the database you may implement them with stored procedures or any other API the database offers to access tuples. With this solution you may write tricky optimizations like Overflow Tables in database code instead of a host language plus embedded SQL. However, you put extra load onto the database server and you have to ensure, that all applications comply to this architecture.
If you have a hard-wired
connection between ConcreteViews
and ConcretePhysicalViews,
you may implement ConcretePhysicalViews
as methods of the ConcreteViews.
However, this solution is less flexible since you are not able to use the same ConcretePhysicalView
twice.
You may also use Physical Views to encapsulate non-SQL databases and file systems such as ADABAS, IMS-DB, CODASYL, and VSAM. As we have mentioned before, you may use this variant to build relational applications on top of legacy databases.
For a further discussion of database optimization see: The Overflow Table pattern describes in detail, how to partially merge tables. Controlled Redundancy contains a discussion on when to grant write access. Narrow View gives hints on Physical Views to select data.