Relational Database Patterns?

pole asks: "Are there common relational database patterns (schemas, stored procedures, triggers, etc.) which can be effectively reused? These could be "upsert" (update or insert) patterns, temporal database patterns (historical data such as human resources records or time-series data), referential integrity patterns or even code generation patterns (good programmers don't reuse code via cut-and-paste). Can you think of useful RDMBS patterns? Would be great if these were part of a repository with use-cases and examples that actually worked. Is there such a beast?"

SQL vs OO by Scott Ambler

[basic70]

Scott Ambler at AmbySoft has written quite a lot about this, especially when it comes to using relational databases in an object oriented system. Check out http://www.ambysoft.com/onlineWritings. html [ambysoft.com]

Despite the fact that many old SQL folks get very upset when they see things like the complete absence of stored procedures and triggers, I know from personal experience that (many of) his recommendations work just fine.

/Basic

my own experience

[Alien54]

My own expereince is that there are many general procedures used in the creation of databases, but that when you get down to the specific demands of a customer or user, it is all fine tuned to specific requirements.

As a general simple example, a basic invoicing system would include a customer table, a saleperson table, an invoice table, a line item table, a partnumber table, an inventory table, etc, etc, etc.

Now we add to this the specific demands of the company such as auto-calculation of local and regional taxes, preferances for data entry screens, the exact data that the user desires to be stored, ease of use issues so that the sales geeks can actually use the system, requirements of suppliers in terms of ordering a variety of items, etc....

It becomes a bloody mess because of the intense customization required. There is no standardized way of running a business. Each CEO/CIO/CFO etc has their own ideas regarding the specific details on how things should be set up.

Thus we have the market for companies that provide business solutions for specific markets and business types. Each one requires a specific expert knowledge of the demands of that particular industry, with all of the quirks that go with it. Within that industry you can something stand could serve as a standard, but it would likely be promoted by a single dominant solution provider of some sort.

Re:my own experience

[Alien54]

as a follow up:

Internal to a company, you can certainly modularize your work to some degree, so you DO have a fighting chance. But things have to be designed from the ground up that way.

Otherwise the terrors of spaghetti code await you.

and spaghetti design, and spaghetti management, and spaghetti specifications ....

:P

Data Warehouse Patterns

[jmaslak]

Kimball's book, the Data Warehouse Toolkit, does exactly this. It gives you a pattern for a star schema, which is almost the only schema used in data warehousing. He then goes into detail about slowly changing dimensions, rapidly changing dimensions, partially summable facts, etc. If you do data warehousing, this is the book of patterns. Maybe I'll write a review for Slashdot sometime...

Relational Database Patterns

[JavaStick]

Actually, quite alot of work was done in this area by a company called Synon (which was later swallowed by Sterling Software). Synon created a product called Obsydian which used both templates and inheritance to allow developers to build reusable patterns largely targeting enterprise scale business apps built over relational databases. the approach was quite successful. in fact, using these patterns one could essentially generate a large system in a matter of days. of course, in using a tool this powerful alot of care must go into the architecture of the patterns as they are essentially reused and leveraged in all the derived patterns. alot of programmers and dbas resist and fight patterns for a variety of reasons but in terms of building enterprise scale business apps they are invaluable in reducing risk and bringing systems online quickly.

Did you check the portland pattern repository?

[Bazzargh]

Take a look at

http://c2.com/ppr/titles.html or http://hillside.net/patterns/EgPatterns.html

for example: 'Stars: A Pattern Language for Query Optimized Schema' and the ARCUS 'Design of Business Information Systems' pattern language.

The latter covers object-relational access layers and the like. Another useful thing to look at (in that your question asked about temporal patterns) is Ralph Johnsons paper on the accounting system he wrote with his students. http://c2.com/cgi/wiki?TransactionsAndAccounts

In defense of patterns (since someone here attacked them): they encourage people to write down what they consider to be best practises in analysis and design, and to say why. Is this so wrong? As Santayana famously said: those who forget history are condemned to repeat it.

Two excellent resources for you

[Beelzebubba]

The Data Modeling Handbook [amazon.com], by Reingruber and Gregory
This is a truly indispensable reference, which never leaves my desk.

SQL For Smarties [amazon.com], by Joe Celko
Joe Celko's advanced SQL programming book is full of solutions to both common and not-so-common modeling problems. His section on trees was particularly useful to me recently.

If you're ever interested in chatting about specific modeling issues, feel free to email me at [e-mail] - don't forget to remove the spam.

One older text

[RobertEdwards]

An older book I've found useful is Case Method Entity Relationship Modelling by Richard Barker. It discusses some rather complex situations and develops some useful general purpose models of patterns he'd identified. It's from 1990 and predates the current interest in formal Patterns, but it appears to me there's some similar thinking going on here.

Since it's a book on modeling, this will be most useful in analysis and design of a database, not directly in the actual construction.

See
http://cseng.aw.com/bookpage.taf?ISBN=0-201-4169 6-4&ptype=0&catpage=&catID=1.103&ctype=sub ject
OR
http://www.awl-he.com/titles/0201416964.html

Baker is or was a VP or Director level at Oracle, and this book is used as a text in some of the Oracle training classes.

Another reference:
http://www.essentialstrategies.com/publications/ modeling/barker.htm

(Great question by the way, I'm off to check those other references!)

Re:One older text

[RobertEdwards]

For what it's worth, I found another text on this subject while surfing around this topic:

DATA MODEL PATTERNS:Conventions of Thought
by DAVID C. HAY
ISBN: 0-932633-29-3

http://www.dorsethouse.com/books/dmp.html

Apparently it further develops the Barker / Oracle CASE methods. And is also not UML.

solution in PHP

[eries]

Over here at the Enzyme open-source project [sourceforge.net], we've been working on several libraries that help PHP developers access data in a RDBMS using OO techniques designed to promote code reuse. We don't have a lot of theoretical stuff done yet, but we're giving away a lot of code :)

datawarehousing patterns

[neutrino.tea]

This books is reasonably good at providing some lowest common denominator models for the reporting end of data access. Transactional database of all creeds can be used to feed these reporting models: The Data Model Resource Book : A Library of Logical Data and Data Warehouse Designs http://www.amazon.com/exec/obidos/ASIN/0471153648/ ref=sim_books/104-0034940-6588767

I can see a pattern here...

[lfourrier]

After more than ten year professionnal practice with relationnal databases, notably DB2, Oracle, Sybase (now ASE), Watcom (now ASA), I think that "paternisation" with the meaning given by the gang of four is not really achievable.
It occured to me, some time ago (in 91, I can be specific cause I know for sure I was not married at that time), that a company could be created to try to sell physical data models permitting to implement systems for different activity sectors (being, for exemple, human ressources, accounting, production...), implementing a known working pattern of datastorage, not of data access.
But at that time, I didn't know about Internet or GPL, and I couldn't figure out a way of making money. And given my enterprenarial unfriendly environment (living in France), I quite forgot about it, being sorry once and once again about the need to reinvent the wheel each time.
There seem to exist emerging way to implement storage, but as they are numerous chapell, there is not one way to do things.
And I think an important cause of this state of facts is caused by the absence of one RDBMS implementation.
So each one has it's strengths and weakness. And so, for each DBMS, what is working great, in term of data integrity, performance, ... can be a killer or completely unfeasible on another DBMS. So a project manager who is an experienced Oracle developper can be a problem for a Sybase development (or the inverse), and a a developper experimented with an old version of a DBMS can miss important novelties of a new version.
Perhaps some tendency like SQL92 (imagine, a standard created eight year agos and not yet fully implemented in major commercial products) are steps in the good direction toward the ability to have reusable patterns of database access and storage, but for the time being, the main choice is beetween multi database compatibility and efficiency.
And given the tight time constraints of the majority of projects, the easiest way for the management is often to use competent developpers specialist of the target DBMS, with lots of hands-on experience, permiting them to reinvent the wheel, but with the implied knowledge that they will not create costly (in term of debugging, maintenance and tuning) performance bottlenecks, but will instead use every really working feature of the target DBMS (that is, forget marketing hype about features) to go as quickly as possible (even if not the less costly in the long run) toward the creation of the system expected by the client.
So, should you wisch pattern for databases, they are in the minds and conversations of experienced developpers, but they are more Oracle 7 patterns, Oracle 8 patterns, Sybase pre-10 patterns, Sybase 10-11 patterns, sybase 11.5 patterns (with row level locking) ... and so on.

Database Design Patterns

[timcuth]

My comment is that the RDBMS paradigm is in an of itself a set of patterns devised to help create complex and efficient data storage and retrieval.

However, I can see a need for what you are requesting because, as a RDBMS DBA since 1986 (DB2, Access, now Oracle) I see more horrible implementations than good ones. It seems that most designers have a strong preference for ignoring the known good practices instead of following them.

I currently work on a large project where the application is supplied by a major vendor (name omitted to protect the guilty). Its central table accounts for more than 95% of all data access in the system, and more than 95% of those accesses are full table scans (and it is a 2 GB + table with more than 10 million rows). Why? Because the table is horribly denormalized with 2 sets of 12 columns each, for two attributes for each of the months of the year. Also because about ten other columns comprise the primary key.

Everyone needs to read and understand Chris Date's book on relational concepts (I don't have the title conveniently available). This book thoroughly describes and explains almost everything anyone would ever need to know about relational design, independent of any particular RDBMS implementation.

Re:Database Design Patterns

[timcuth]

The book I referred to:

An Introduction to Database Systems - 7th Edition
Christopher J. Date
Addison-Wesley
1999