When I’m building views in SQL, I tend to do one of the following:
-
Views that contain logic and criteria for selecting and joining records. I.e. “what records am I interested in and how do they fit together?“
-
Those that focus on returning a certain set of fields, sometimes formatted in a particular way. i.e. “for these records, what details do I want?“
The former tend to be very light in terms of their output. Typically just foreign keys.
In my head this is somewhat based on / related to the Single responsibility principle, but it’s not quite the same.
Is there a recognised pattern that fits the bill for this?
To pre-empt a question: I know not everything needs a name, but in my case it would be helpful to explain this in our conventions document.
6
Incorporating enough fantasy, I am pretty sure people will find some similarities in well-known patterns. However, just because your approach is in coincidence with some GoF pattern details I would not go so far and call your separation of views an “Adapter” or “Facade” in the GoF sense. Those are patterns specifically for object oriented programming, and when you start using these terms for SQL views, expect misunderstandings when talking to other people.
However, what you describe looks to me just as an example of the well known software design principle “separation of concerns” – no more, no less.
2
Coming from a database background, I can’t say that I have ever expressly heard a DBA or database developer actually use the term “pattern” before in describing their schema design. “Design patterns” are really more of a programming concept and there is a good question on SO that explores this.
To be fair, you can certainly have design patterns on your database but I might argue that this can get you into as much trouble as help you. Design Patterns are tools for application programmers to help solve unique and varied problems. Relational Databases and Set Theory on the other hand is more structured and centralized all around data integrity. Design best practices are generally accepted (i.e. Normalization) so clever design models such as EAV are typically frowned upon by die-hard DBAs.
Coming to your question around views, what is preferable is going to depend on what you are trying to do. You basically have two kinds of processing:
OLTP - OnLine Transactional ProcessingOLAP - OnLine Analytical Processing
For OLTP, unless you are using views as part of your security model (to restrict certain user role access), you really should only ever see views of the #1 variety above (the idea being that if your data is properly normalized, most worthwhile data models will require multiple joins as the entities have been separated to eliminate transitive dependencies).
For OLAP, however, your views should be a part of your Data Warehouse/Data Mart model. There are a lot of varied techniques on how to perform this with terminology and recommended best practices changing depending on the Business Intelligence Tools you use. The long and the short of it though is that you are transforming your data (usually through dynamic or materialized views) to create a data model which makes it easier for end users to extract information. The best way to do this is going to revolve mostly around what type of data you are using. But again your original data should be highly normalized so I would expect many more of the #1 variety over the #2.
TL;DR – Database best practices are generally more accepted than having a bunch of design patterns available to the DBA. Given this it would probably be worthwhile to spend some time reading up on generally accepted practices related to Normalization, Data Warehousing, Data Marts, ETL and OLTP/OLAP.
The first case creates a certain set of data, the model of which differs from the original database. This is most likely done for the purpose of reading the data in the easy way from some third party side that uses a model similar or identical to that of the view. That seems like an Adapter pattern to me.
What you described in the second case reminds me most of the Facade Pattern
Basically, it simplifies underlying interface into something that is more easily readable from the outside. In your case, you have a set of tables in the database that contain data that you want presented in a certain, easy to read, filter and interpret way. To that end, you create a view that wraps up all that data, scattered across multiple tables in one, simple, easy to read and use view.
6