Relational Database Patterns? 14
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 (Score:3)
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.
my own experience (Score:2)
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 (Score:2)
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 ....
Data Warehouse Patterns (Score:1)
Relational Database Patterns (Score:1)
Did you check the portland pattern repository? (Score:1)
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 (Score:1)
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 brd27spam@hotmail.com - don't forget to remove the spam.
One older text (Score:1)
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-416
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
(Great question by the way, I'm off to check those other references!)
Re:One older text (Score:1)
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 (Score:3)
datawarehousing patterns (Score:1)
I can see a pattern here... (Score:1)
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,
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)
Database Design Patterns (Score:1)
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 (Score:1)
An Introduction to Database Systems - 7th Edition
Christopher J. Date
Addison-Wesley
1999