[Gllug] A programming question. long.

[Alex Hudson]

(Edited again. Reason: I'm using OE at my Dad's house; mutt signed
attachments don't get included in 'Reply'. Normal service resumes on
Monday.. :)

>> It's not 'invalid', it's just a bad habit to put business logic in a
database.
>On what grounds is it a bad habit?

On the grounds that the database is not aware of the context in which a
transaction happens; it is just a filestore. On the grounds that such as
design does not scale.

>> The only excuse you can have is to use import application into the
>> db server (stored procedures, triggers, etc.) for performance reasons,
much
>> like denormalisation. But it should be avoided where possible.
>On the contrary, putting the logic into the database increases the load
>on the server.

I didn't say it made the database faster (although in some cases it would),
it makes the system perform better. If you want a sorted result, for
example, you don't have the result set returned to you and sort it yourself,
you ask the db to do it - it's faster. If you want to see rows which aren't
being 'dealt with' (in your example), you use a where clause, you don't have
the whole set returned to you. Etcetera.

>> That wasn't my point - my point was a database cannot validate meta-data,
>This assumes the database is designed in ignorance of the uses to which
>it will be put.

No, it doesn't. It acknowledges the fact that the application is dealing
with information at a higher level than the database, and as such has a
better understand of what is going on. The database is there to enforce
stuff like referential integrity, constraint satisfaction, etc. However, the
database cannot guarantee that the data it holds makes sense, beyond those
few validations. It can say the data is within the design of the database,
no more. Databases deal with data, applications deal with information.

>The database should enforce credit limit rules - if it doesn't then any
buggy application
>(or fraudulent access) may be able to steal credit.

I don't agree with this at all. Your argument is a web-of-trust based
argument, nothing to do with system design. By moving everything into the
database, sure, we now have a trusted system (since there is only one
component.. ), but in the real world, that's not how things work, because
such a system is not practical, and to suggest that is the only secure
solution is naive at best. A database needs to trust applications, true, but
that trust doesn't need to be blind, which is where your security argument
falls down.

A simplistic example: how does the database tell the difference between a
transfer of funds between account A and account B, and separate debit of
account A and credit (of equal value) of account B? Answer is there is none,
only the application (which is dealing with the meta-data also; in this case
the context of the transaction) is aware. Of course, in this case, it is
possible to generate a stored procedure to deal with transfers, and another
to deal with debits, another to deal with credits, etc., but this is only
possible due to the simplistic case set out here, and is also another
example of moving application into the database - there are many (more
complicated) examples where it is impossible to the db to distinguish
context, and as such, it has to be the application which deals with the
logic.

Putting business logic in the database only works for simple systems - once
you have something even vaguely complex, it all falls down because you need
to generate ever-higher level functionality within the database - at which
point, the database becomes an application server and you are back to the
old client-server model which is so far past it's sell-by date it's untrue.
Designs like that don't scale - stored procedures are not RPC, they are
there soley to improve system performance, that is the only reason.

Procedures and triggers are fundamentally limited in what they are able to
do (on many dbs, for example, procedures are not re-entrant), are limited in
grammar and vocabulary (not being able to 'use' in some dbs), generally
don't cascade, etc. Even moderately complex business logic would be either
unfulfillable, or place an intolerable stress on the database.

And this is exactly why I think it is a bad habit. The design doesn't scale,
the security model doesn't scale (your web of trust relies on having only
one participant), which means this is only suitable for small-scale simple
solutions. I also hesitate to think how a database being controlled by procs
and triggers would be replicated in a valid state, because that is also
non-trivial.

Applications should control the business logic of data, and use transactions
to transit the database between valid states. Stored procedures, triggers,
et. al are all hacks to generate the performance necessary of a real-world
system - they are not strictly necessary, but like denormalisation, we swap
the perfections of design for real-world speed.

Cheers,

Alex.



-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug