More on Validating the Data Model
In response to my columns on the qualifications of the data modeler, a reader inquired about who is responsible for validating the data model. In the last column from the March/April 2011 issue of Information Management, we examined the responsibilities for the business data model; in this column, I will explore the validation responsibilities for the technology model.
Unlike the business data model, which presents information from the business perspective, the technology model, also known as the system model or physical model, provides the structural information for the data structure that needs to be created.
The technology data model is specific to an application and platform. A first cut of this model is often created by the data analyst who worked on the business data model, with the refinements provided by a database administrator. Validation activities for this model include:
- Completeness: Does the model have the needed elements to satisfy the requirements of the application being built?
- Standards conformance: Do the physical table and data names, data types, table parameters, etc., obey the shop's standards for these items?
- Security: How does the proposed structure (e.g., partitions) support compliance with security requirements?
- Indexing: Are appropriate indexes recognizing the needed balance between data update and data access?
- Generalization and specialization: How are the logical supertype/subtype relationships transformed into the physical structure?
- Data lifecycle: How is the lifecycle of data (e.g., purchase order, sales order) implemented? To what extent are database constraints used to ensure appropriate population at different points in the lifecycle?
- Referential integrity: How is referential integrity enforced?
- Auditability: How is the audit trail for data (when required) provided?
- Keys: Does each table have a unique key? Are appropriate alternate keys defined? Are surrogate keys appropriately used? Are compound keys redefined when needed to improve performance?
- Denormalization: How has the structure been denormalized to satisfy usage and performance objectives?
- Optimization: To what extent does the structure take advantage of database management system optimization capabilities?
The validation activities are all related to the physical structure of the data. As such, validation of this model is the responsibility of IT personnel, and the primary roles belong to data analysts, database administrators, security administrators and programmers.
The data analyst review is focused on two major factors: to ensure that nothing was lost in transforming the logical model into the technology model, and to determine if any of the adjustments made in the technology model need to be reflected in the logical model.
The database administrators and programmers recognize that some of the integrity constraints (e.g., referential integrity, domain values) can be established either through the data structure or through application code. They must work together to ensure that the above criteria are satisfied through the combination of the two.
A database administrator, other than the one who created the model, should provide a second set of eyes on the model. In addition, the programmers should review the model and suggest potential adjustments based on their understanding of the implications on the application code they are developing.
Often, there are multiple correct technology model representations, and the choice can significantly impact the programming effort. These reviews can be done through a review of the model and a facilitated peer review session. If a peer review session is conducted, the data analyst who created the model should also participate.
The technology data model describes the physical data structure. The content should be reviewed by the creator of the model from which this was derived and technical people familiar with the application, the shop's standards, and the DBMS and platform on which the database will reside.
I welcome feedback and input based on your thoughts and experiences related to validation of the technology data model. Contact me at email@example.com.