1

When I'm building views in SQL, I tend to do one of the following:

  1. 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?"

  2. 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.

  • 1
  • 1
    i would say that Views are Views, the first seems to be more focused on Business, while the other seems more technical. – Walfrat Sep 14 '16 at 11:35
  • Thanks for linking @ThomasOwens - to be clear, I'm not asking what this should be called, but whether a pattern exists that describes the approach. – Tom Wright Sep 14 '16 at 11:37
  • I'll reopen and see what the community thinks. It seems to be right on the edge of what may be an on-topic question. – Thomas Owens Sep 14 '16 at 11:39
  • Have you got an example use case for 2? I've never used an SQL view for that. I use SQL to extract data unformatted, then use the calling language to do the formatting. Unless you have a particular use case, I recommend you do the same. – paj28 Sep 14 '16 at 14:26
4

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.

  • I toyed with separation of concerns as well as with SRP. If there's not a more specific answer, this may be what I go with. – Tom Wright Sep 14 '16 at 15:28
  • 2
    +100000 for stating that named OOP patterns do not apply to database design. – Tulains Córdova Sep 14 '16 at 15:29
3

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 Processing
  • OLAP - 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.

0

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.

  • Thanks Vladimir. The facade pattern seems to apply nicely to the second of my two use cases. Are you aware of a design pattern / style / convention that applies to the distinction between the two types of view? – Tom Wright Sep 14 '16 at 13:45
  • I edited my answer to cover the first case too. – Vladimir Stokic Sep 14 '16 at 14:09
  • Doesn't the adapter pattern involve an interface or abstract class or are we talking metaphorically? – Tulains Córdova Sep 14 '16 at 14:11
  • @TulainsCórdova - In the case of relying on views, you could think of the tables as abstract classes especially if the security design prevents any direct usage. – JeffO Sep 14 '16 at 14:46
  • @JeffO I agree with you in that "depend on views, not on tables" is very similar to DIP but equaling the use of database views to an implementation of any of the widely known named OOP design patterns is, at most, mataphorical. – Tulains Córdova Sep 14 '16 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.