Developers routinely use data models for defining database structure. This is beneficial, but it uses only part of data modeling’s power. Data models not only capture data structure, but they also express the potential for computation. Traversals of models can provide blueprints for resolving use cases, phrasing SQL queries, and assessing quality.
Traversal as a Dialog for Resolving Use Cases
When developers are building applications, customers often present requirements in terms of functionality that is stated in the form of use cases. As we develop a data model, we walk through it with customers to make sure that it addresses their functionality needs. We do this by playing each use case against the model. Does the model have the entities and relationships to support the use case? Can we traverse the model in a manner that pulls together use case data?
This kind of interaction serves several purposes. We can validate a data model while deepening our understanding of the use cases. The reconciliation of a data model with use cases helps to clarify both. Some developers focus on use cases alone, but it is better to address data and functionality in tandem. Navigation lets us exercise a model and uncover hidden flaws and omissions so that we can repair them.
As we reconcile use cases against the evolving data model, the customer can see that we are taking their requests seriously. They can see that we understand them. And they can see how the data model contributes. If there is a problem with the model, they can often help to fix it.
Traversal as a Guide for SQL Queries
We also use data model traversals as a blueprint for writing SQL queries. Model traversal provides a scaffold for writing SQL.
- First we determine the relevant tables.
- Then we connect them with joins. Model traversals manifest as joins from foreign keys to primary keys.
- Third, we specify the desired columns.
- And finally we apply filters.
Of course, there is additional complexity for nested queries, aggregates, and so forth. But this basic approach provides a good start. We often see others using GUI editors to write SQL queries. But it is really quite easy to write SQL if you use a data model as a guide and follow the steps listed above.
Traversal as a Means for Assessing Quality
Robert Hillard in his book Information-Driven Business assesses the complexity of a data model by likening it to a graph. Database tables are the nodes. Foreign key to primary key relationships are the connections between the nodes.
One of Hillard’s complexity measures is the traversal length from one node to another in a data model. For example, is the pathway from Order to Customer a single relationship? Or is it a series of relationships? Models that have long traversals are more difficult to understand, more error prone, and more costly to work with.
Data models are helpful for defining database structure, but don’t overlook their capability for resolving database functionality. A model is not just data structure. It also embodies the potential for computation. As you traverse a model, you can see the latent use cases and SQL queries. In addition traversals can provide deep insights into complexity and quality.