The INFORMATION_SCHEMA views are a standard set of views that most major DBMS provide. What is the name for this design pattern?

For instance, let's say there are a dozen source databases for supermarkets (Safeway, Superstore, etc) and you want to be able to query all of the databases using a common query. Is there a name for the pattern (or paradigm, etc) where each of these source databases would implement a set of standard views?

Please note this is a purely fictitious example, and I am not looking for opinions on whether or not it makes sense as compared to using and ETL tool, or a data warehouse etc.

  • Is the word you're looking for metadata? – Aaron Bertrand Apr 21 '15 at 23:26
  • @AaronBertrand No, it is not. – jzacharuk Apr 22 '15 at 1:45

I think what you're looking for is "different objects, same interface". This facilitates code (in this case, SQL) reuse. This is known as the adapter pattern - see here.

I think that this quote encapsulates the thrust of your question

In software engineering, the adapter pattern is a software design pattern that allows the interface of an existing class to be used from another interface.1 It is often used to make existing classes work with others without modifying their source code.

[EDIT in response to OP's comment]

Interesting question from a theoretical computer point of view. However, from my relatively rudimentary perspective, design patterns are more suited to procedural, OO and functional programming paradigms rather than to database work, SQL operates on a declarative paradigm. The SQL language IMHO, does not lend itself to the construction/implementation of programming design patterns.

There is one interesting approach - identifying RDBMS patterns (not necessarily design patterns in the programming sense) by what they are not. Bill Karwin (a Percona employee and respected contributor to this group) has written a book called SQL Antipatterns. The book is here, but if you want to sample before purchase, there's an (impressive) 250 slide presentation here, also by Karwin.

So, to clearly answer your question, there is not a 1-1 correspondence between programming design patterns and RDBMS SQL patterns, and I don't know of any database-field specific terms in this area.

  • That is the type of pattern that I was looking for. And I am familiar with it from a programming point of view, I am curious if there is a database specific term though. – jzacharuk Apr 22 '15 at 1:46
  • 1
    @jzacharuk - Please check my edit to my answer. – Vérace Apr 22 '15 at 2:21

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.