I'm going to take a position in this post, and I welcome support or
refutation of my position. I also strongly request references to books
and web links on this subject. Here it goes:

A database driven application should be modelled with normalized
tables with a mind towards OLTP processing. Developers can select from
the database in anyway they want, but are provided no means to insert,
update or delete database data.

Instead, all updates and deletions are provided to the developer via
stored procedures. The motivation for controlling insert/update/delete
is to ensure that all changes to the database preserve entities and
relationships. It also prevents developers from "E-R Creep" --- that
is, it prevents developers from fattening their controllers and views
with implicit entities and relations that they identify and build in
program code in an unstructured, undocumented fashion.

It is important to realize that an application should have a MODEL not
a RELATIONAL DATABASE. A database access layer is not a model. Program
code that subtly creates entities and relations is not a model. A
MODEL is created via a well-normalized E-R model whose integrity is
guaranteed via well-documented mutation via DBA-developed stored
procedures only.

This ( http://perlmonks.org/?node=How+aggre...development%3F
) is a related post I wrote up on the same subject.

On Thu, 13 May 2010, metaperl wrote:

Including in development? Not being able to add and modify test data would
be a huge pain. Even if you wanted to apply this philosophy in production,
i'd suggest giving developers free access to the databases they use for
day-to-day development - but enforce the rules in the nightly build
system, so they learn not to use those powers in the code they check in.

I don't understand either of those concepts - either the idea that changes
to the database might not preserve entities and relationships, or E-R
creep. Could you give me some examples?

To be honest, my visceral reaction is that you are exactly the kind of
nutjob DBA who makes actually developing software a nightmare. However, i
am conscious that this is my guts talking, not my brain.

As a developer, my main concern would be slowness of development. If i'm
working on some feature, and i realise i need a new way to modify data, i
have to wait for you to write a stored procedure for me, rather than being
able to just do it. It's the same problem you get if you restrict the
ability to to DDL to DBAs. Databases aren't magic - they're just another
one of the components that have to be cajoled to make a working system.
The most effective way to build working systems is therefore to let the
people doing it cajole any of the components they need to. Still, you
might decide that the overhead was justified by the improved data
integrity or whatever.

That's an avenue i'd be interested to see explored: when is this mutation
monopoly approach useful, and when is it not? Perhaps for an application
with a simple data model that won't be subject to much analysis (a social
networking app, perhaps), it's not necessary, but for something where a
very complex domain is being modelled and the full analytical and
reporting power of the database needs to be applied (a financial
derivatives brokerage app, perhaps), it could be useful.

Would an alternative way of avoiding the problem be for the DBA to be
involved in code reviews, architecture, design discussions, etc? They
could be a 'keeper of the flame' for good database design, helping keep
everyone else on the straight and narrow. Programmers within a development
team often take on roles like this already, even if it's not formalised -
there's the guy who insists on doing exception handling right, the guy who
always makes a fuss about thinking through the impact on the build and
release process, the accessibility crusader, etc.

Taking this to the extreme, if the team was practicing pair programming,
then perhaps when tackling a task that involves changing data mutation, a
developer and the DBA could pair on it - they could collaboratively
explore and determine out the best way to do it, meeting both sides' goals
and transferring understanding.

Have you thought about the interaction with object-relational mapping? If
the developers are using an ORM layer to interact with the database, then
(a) is restricting mutation to stored procedures viable, ie can you
configure ORM engines to do mutation through them and (b) is it still
necessary, or will the ORM naturally restrain the design to a
well-normalised state (or will it do the converse)?

tom

--
No gods, no masters.

In article <b1813a63-d1f0-44b2-8853-eba10b6050a0 (AT) o8g2000yqo (DOT) googlegroups.com>, metaperl <metaperl (AT) gmail (DOT) com> wrote:
procedures. While sprocs were fast, they didn't scale very well. They needed
our products.

Databases should have data, and integrity constraints to keep the data clean.
Business logic in the form of stored procedures does not belong in the
database.

Sprocs are difficult to debug, maintain, and are not portable. Certain db
intensive activities may make sense to be writted with sprocs to avoid the
overhead of roundtrips, like reporting, but these should be the exception
rather than the rule.

Eric

eric (AT) invalid (DOT) com (EricF) wrote:

That is a purist's view and one which I agree with. However the marketplace
has spoken and programmers are mostly idiots. Their managers, also idiots,
want cheap and easy, not "doing it right".

On 14.05.2010 06:17, EricF wrote:
On what database did you have those SPs and why didn't they scale?

As always statements like these should be taken with a serious grain of
salt. Unfortunately often you cannot ensure data integrity completely
via constraints. You can do a few things via triggers but these are
highly DB specific as well.

When I think of those applications whose performance has been crippled
via bad usage of EJB 2.1 then a clear procedural interface to the data
written in stored procedures all of a sudden looks very attractive to
me. You can save a lot of roundtrips that way. Granted, with modern
EJB 3 you can make DB accesses a lot smarter and more efficient - but
the general mismatch between the OO world and the relational world remains.

These are really bad examples I have seen but the fact that development
speed is reduced because you cannot easily change something can actually
be positive. I know, all those time to market gurus will tell you
differently but risking the integrity of the most important thing a
business has in its IT world (its data) can have dramatic effects.

While it may be good to be able to quickly extend the user interface
this can have extreme adverse effects when applied to the data. The
downside of the quick schema change is the schema migration that seems
to be forgotten so often. The change that seemed so smart on the dev
system might all of a sudden turn out a major nightmare with the
customer's 56GB database which cannot afford downtimes of more than 5
minutes.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

In article <855b3bF3ioU1 (AT) mid (DOT) individual.net>, Robert Klemme <shortcutter (AT) googlemail (DOT) com> wrote:
Robert, I've seen scalability issues with Oracle and SQL Server where there
was a lot of processing with sprocs.

I think it is okay to put some logic in the database but very little, and that
needed only to insure integrity. Databases have been optimized to work with
data. When you start running business rules there, that is where you can get
into trouble.

EJB 2.1 can be abused as can any language. You can write miserable code in
PL/SQL and T/SQL. I think it is a lot easier to write bad code in the sproc
dialects because they are not really meant to be general purpose programming
languages.

I recently saw Martin Fowler, 1 of the agile gurus. He was advocating a
database per application so you could move faster and the risk of bad data is
localized. I do think that makes sense - for certain classes of applications.

I worked for a company that had most of the BL in PL/SQL. The marketing people
and development managers were unhappy because it took 6 months for the
smallest change to be made. The new developers were unhappy because we were
charged with moving the logic into a n-tier Java application - but we couldn't
understand how the spaghetti worked.

Your points are well taken. If you have a 56GB db, you don't want to make
unneeded migrations. And data integrity is key. But don't make developers jump
through hoops to get things done. And think about the ease of maintaining and
the ability to scale when you design your systems.

I was consulting at Turner Television in the late 90's. They needed an
"advanced" search application where the user could select movies that were
westerns and had John Wayne or dramas directed by Howard Hawks with leading
ladies like ... you get the picture. We were constrained to doing the logic in
PL/SQL. Luckily you could generate dynamic queries in PL/SQL. There were
battles getting the DBAs to allow this. The string processing was ugly as sin.
It actually worked reasonably well but it was unsupportable. This should have
been coded in C or C++ or anything but PL/SQL. But the database team had these
"Thou shalt" edicts that didn't always make sense. I hoped they stayed with
Oracle. Porting that would be a nightmare.

Eric

Eric

On 15.05.2010 05:37, EricF wrote:
Interesting.

I believe it is an interesting question where exactly you put the border
between "business rules" and "data integrity enforcement". I believe
that many projects do not consciously draw that line.

PL/SQL is a very rich language with a similar set of features like other
procedural languages - and it is even better in some areas. C does not
have exceptions and packages which PL/SQL has. I think you can write
pretty good structured code in PL/SQL as in any other language.

Yes, of course. I would consider that given - lumping different
applications' data in a single schema is certainly a bad thing - and
depending on application functionality that statement readily extends to
a single database server (just think about different tuning options for
different applications).

That is probably not PL/SQL's fault. As you said, you can screw up with
any tool you use. :-)

I fully agree. And scaling is usually easier with application servers
than with databases. Although Oracle offers RAC there are not much
other RDBMS that can do this. Replication can often be done but fully
RW access on all nodes is a feature not wide spread.

As always it's a question of balancing different things out. I believe,
the schema and changes thereof should be done with much caution. As
long as that is ensured via organizational measures application
development in a non database language is probably the most efficient
approach. But DB knowledge should be consulted there as well,
especially when it comes to querying potential large sets of data.
Unfortunately this seems to be often neglected and comes back later to
haunt everybody.

:-) I don't think it *has* to be spaghetti and I don't believe this was
due to PL/SQL. I would rather write an application in PL/SQL than in C
(which was one of your suggested programming languages). And you can
make it reasonably efficient if you watch out for proper handling of
procedure and function arguments (avoid copying for example).

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Sun, 16 May 2010, Robert Klemme wrote:

I suspect the point is that there is a school of thought that doesn't see
it as an application's data, but rather sees it as the data's application.

At the moment, i'm working on an ecommerce site that integrates with some
impossible number of backend systems. One is the system used to input
catalogue enrichment data - not the basic names and prices of products,
but the more marketingy stuff like descriptions, images, and so on. One
approach to that would be for that app and my app to share a database -
we'd read from it, and the enrichment system would write to it. Another
approach would be for it to have its database, us to have ours, and for
there to be a data feed between them. I suspect a DBA would view the
shared database as the most natural solution; Fowler, being a programmer,
is arguing for the feed approach.

If the app servers are using a database for storage, then i'm not sure how
much that matters. You may be able to scale out the logic on the app
servers, but if the app is write-heavy, the database can still become a
scaling bottleneck, and there's nothing the app servers can do about that.
This is why truly humongous sites like Google, Facebook, etc have had to
find new ways of storing data. And why banks and airlines keep paying for
System/390s!

tom

--
The glass is twice as big as it needs to be.

On May 13, 12:54*pm, Tom Anderson <t... (AT) urchin (DOT) earth.li> wrote:
sure

1 - changes to the database might not preserve entities and
relationships

Ok, if you have 2 entities joined by a primary-key foreign key
relationship and if you can never add a row to one table without a
related row in the other, and if there is only an advertised stored
proc which does the INSERT in a transaction to both tables, then the
preservation across tables is guaranteed. If developer code can write
to the database, then one table might be written to without the other.

2 - Entity-relationship creep: here's a good example. You pull 100
rows from a customer table. And then you write a loop in your program
code, skipping over customers based on a series of if-thens, and then
do something with the remaining customers... basically what has
happened is that you were looking for some subset of the customers,
but did not specify that subset with a well-defined and business-
documented label. Instead you created a sub-entity of customers on the
fly with no documentation for that sub-entity.


metaperl (me) is a Perl programmer --- http://www.metaperl.org

It's just I worked at oracle, which is where I learned the "read any
way you feel, updates are only via stored procedures that I look over
before they get added to the db"