Question Re: Are there "SQL Design Patterns"? in Technology Q&A

Are there "SQL Design Patterns"?

VolkerBarth — Wed, 03 Feb 2010 10:44:57 GMT

I have just searched through StackOverflow but couldn't find much...

To all the SQL experts,

is there anything comparable to the "design patterns movement" which has taken place in the programming language world since 1994? You know, the "Gang of Four" book and the like.

I'm asking because I feel the need to structure/organize typical SQL constructs for educational purposes. The question is focussing primarily on querying, not on data modeling.

These possible patterns might classify

when to use a correlated subquery vs. a join,
when to use a derived table,
when to use an union vs. a disjunction

and the like. Such rules of thumb may be something like

If you want to get the rows with the maximal column value c of all rows in table T, use a join on a derived query with group by max as select T.* from T inner join (select ...)...

(I don't claim this is a valid solution, it's just the way I would like a sample to be.)

And I expect them to be general approaches though the possible solutions will obvioulsy depend on the features of the SQL engine - i.e. are WINDOW functions available). In that respect, I would prefer solutions working with SA 11.0.1 and above:)

Any hints are highly appreciated!

[Just do clarify: Inspite of the heavy usage of "like" and "pattern" in this question, I am not at all refering to pattern matching:)]

Re: Are there "SQL Design Patterns"?

Breck_Carter — Wed, 03 Feb 2010 14:36:12 GMT

No personal offense intended, but I'm tempted to create the tag "architecture-astronaut"... 🙂

Re: Are there "SQL Design Patterns"?

Breck_Carter — Wed, 03 Feb 2010 14:37:19 GMT

Seriously, why don't you post some individual questions, like "When should a correlated subquery be used instead of a join, and vice versa?"

Re: Are there "SQL Design Patterns"?

Breck_Carter — Wed, 03 Feb 2010 14:39:28 GMT

Um... try googling "sql design patterns"

Re: Are there "SQL Design Patterns"?

VolkerBarth — Wed, 03 Feb 2010 17:13:00 GMT

Dear Breck, I appreciate your comment, but I am really interested whether users of this fine website have any hints to helpful books on these topics, or what iAnywhere experts have to say. It's definetely not an individual question I have but the general question if there is some valuable collection of such questions/answers. - And I try to keep grounded:)

Re: Are there "SQL Design Patterns"?

VolkerBarth — Wed, 03 Feb 2010 17:20:32 GMT

Just to add: There seem to be some collections of particular problems when using certain types of queries in Oracle, MS SQL and the like, cf. the site **http://explainextended.com. I don't know if there is something comparable focussed on SA (besides the NGs and this site). As stated, it's more for educational purposes*, not to find a way how to formulate one particular query. For the latter, I would (and will!) ask that particular question here.

Re: Are there "SQL Design Patterns"?

Breck_Carter — Wed, 03 Feb 2010 20:10:36 GMT

FWIW I have a fairly large number of what I call "templates"... snippets of SQL in text files arranged by meaningful file and folder names, which I use all the time in my own work (and often copy and paste into forum answers). I imagine that some other folks might have the same thing, a personal "toolkit". I have often thought about publishing them, but that would be a large effort... much cleanup would be required, and the only true motivators (fear and greed) are missing 🙂

Re: Are there "SQL Design Patterns"?

Breck_Carter — Wed, 03 Feb 2010 20:12:00 GMT

...and besides, "templates" fall far short of "design patterns" even on a good day 🙂

Re: Are there "SQL Design Patterns"?

Former Member — Fri, 05 Feb 2010 20:41:23 GMT

In a word, the answer is "yes" though I think many references merely scratch the surface. As one example, a relatively new book by Vadim Tropashko offers solutions to the following patterns:

Counting
Conditional summation
Integer generator
String/Collection decomposition
List Aggregate
Enumerating pairs
Enumerating sets
Interval coalesce
Discrete interval sampling
User-defined aggregate
Pivot
Symmetric difference
Histogram
Skyline query
Relational division
Outer union
Complex constraint
Nested intervals
Transitive closure
Hierarchical total

but as useful as these patterns may be, to me these are merely a part of the problem. In my view, "Design Patterns" with relational databases must include both logical and physical schema design since there are always tradeoffs between the SQL constructions one might use and aspects of the physical schema that render the queries (or updates) possible (or not), and what their performance characteristics may be.

Re: Are there "SQL Design Patterns"?

Breck_Carter — Sat, 06 Feb 2010 12:02:49 GMT

Some more information and reviews: http://www.amazon.com/SQL-Design-Patterns-Programming-Focus/dp/0977671542/ref=sr_1_1?ie=UTF8&s=books&qid=1265457511&sr=1-1

Re: Are there "SQL Design Patterns"?

VolkerBarth — Mon, 08 Feb 2010 08:36:13 GMT

Thanks for the pointer - I'm gonna have a look at that, as the book seems to be available here, too:)

Re: Are there "SQL Design Patterns"?

VolkerBarth — Mon, 08 Feb 2010 08:40:21 GMT

@Glenn: I agree with your point of view that designing queries can't be separated from the schema design. - But sometimes you have to work with an already fixed schema (of a 3rd party vendor's application), And that's what I am dealing with currently:)

Re: Are there "SQL Design Patterns"?

former_SQLA_member1694871 — Fri, 13 May 2011 16:50:07 GMT

Dang! After over a year, you would think they would have some used copies in the $20 range. No chance. This must be a great book. You're still paying over $50 for a used one. 🙂

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN