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

Comments

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

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

solution in PHP

[eries]

Over here at the Enzyme open-source project, 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 :)