Small systems tend to grow with new functionality and new access points. When the system grows, the need for data (might even be the same or very similar) and data access requirements grows at a similar rate and becomes more and more complex. A single component might no longer be the only access way to retrieve a set of data this resulting in an unwanted dependency between different parts of the code and anti-encapsulation.
Let’s consider a simplified scenario for an airport, to show list of departures in the check-in terminal. A Stored Procedure ListDepartureFlightInfo is used to access the departure table and list flights. The system is a service with one access point.
Figure 1. Simple sp no dependency
Now suppose a data warehouse wants to access the same departure table (yes I know it’s not best practice to do reporting on a live database). We add a new access point to support it, in order to avoid limiting our self by using the same access point and thereby the need to update both systems if we make a change.
Figure 2. Simple sp dependency
Now let’s suppose the flight information display board only want to show which flights that has departed or not. And this change to require a new field – [HasDeparted] [bit] NOT NULL - in the Departure table. But then Flight Info data warehouse also might need to be updated, in order to handle new column in the result, depending on how well it’s written!
This has added complexity that has to be managed by the development team. We need to regression test both access points with the updated data access layer and new column and in deployment we only need to deploy database, both access points and even the data warehouse to handle the new column.
There are a several ways to get around the problem, each with its own advantages and disadvantages. Here are three possible "solutions".
Alt 1) By adding a new Stored Procedure we essentially break the direct dependency between the access points and the SP/Table implementation.
Figure 3. Breaking up dependency by access point and stored procedure Alt 1
With this solution if component Flight Info requires a database change and to the data access layer, the data access layer can be updated to support the new functionality whilst supporting the existing functionality of Flight Info data warehouse. We need to regression test both access points with the new data access layer but in deployment we only need to deploy the database, new Flight Info and the updated data access layer.
The drawbacks are:
· Duplicated code of two Stored Procedures.
· The loss of the possibility to make general changes, one example can be trimming the query affect both systems.
The advantages are:
· The broken dependency between access points.
· The possibility to make specific changes in the procedure per access point, one example can be to implement different sorting.
Alt 2) By having two data access layers and have one of them return the new column and one ignoring the new column from the stored procedure we also break the direct dependency.
Figure 4. Breaking up dependency by access point and data layer Alt 2
With this solution if component Flight Info requires a database change and to its data access layer, the data access layer can be updated to support the new functionality whilst the data access layer for Flight Info data warehouse is left alone. We don’t need to regression test both access points if the database change does not affect any table that the ListDepartureFlightInfo stored procedure uses or the stored procedure in itself. And in deployment we only need to deploy the database, new Flight Info and its updated data access layer.
The drawbacks are:
· Data access layers code is duplicated.
· No possibility to make specific changes in the procedure per access point
The advantages are:
· The broken dependency between access points
· The possibility to make general changes, one example can be trimming the query affect both systems.
· The flexibility of different data access layers.
Alt 3) Is to combine Alt 1 and Alt 2.
Figure 5. Breaking up dependency by access point and data layer and stored procedure Alt 3
With this solution if component Flight Info requires a database change and to its data access layer, the data access layer can be updated to support the new functionality whilst the data access layer for Flight Info data warehouse is left alone. We don’t need to regression test both access points if the database change does not affect any table that the ListDepartureFlightInfoDW uses. And in deployment we only need to deploy the database, new Flight Info and its updated data access layer.
The drawbacks are:
· Data access layers code is duplicated.
· Stored procedure code is duplicated.
· The loss of the possibility to make general changes, one example can be trimming the query affect both systems.
The advantages are:
· The broken dependency between access points
· The possibility to making specific changes in the procedure per access point, one example can be to implement different sorting.
· The flexibility of different data access layers.
· Easer to modularize the code
Summary
As a system grows we need to break up the database and access code into smaller more manageable chunks: by making active choices, use grouping, components and to modularize the code and always look out for anti-encapsulation and dependencies between different parts of the code and if needed to-do refactoring.
