Databases as Services
Databases as Services typically are well encapsulated and contain business rules
Without discussing if the kind of database design Barry is
suggesting is "Service Oriented" or not, I think the idea
of a well encapsulated database with business logic is
interesting. It follows Jim Gray's Put the computation near the data principle. It goes against the idea of the 'middle tier'
where the business logic is performed to avoid loading the
database server.
However, most 'modern' features of relational databases
(referential integrity, joins) are usually performance
intensive. People who need to get the most of them,
don't use those features, but most of us use them because the performance impact is
not that bad in our scenarios, even if it increases the load
of the database. It looks then that moving the logic to the
database could be acceptable in some cases.
Going back to the database as services matter, the main
difficult that we have with SQL Server 2000 to make this
work is that services need to deal with hierarchical data,
and T-SQL stored procedures suck at it. I cannot have a
stored procedure that receives an Order. I need one for the
Order header and another for the Order lines (I know, I
could send the header and lines in a string and parse them,
but that's what 'sucks' mean). That is not encapsulation.
In SQL Server 2005 we have could handle in two ways.
One could be using UDTs, but I'm not sure if I can have an
Order UDT. The other option is to send it in an XML.
When you want to update an Order, and you are sending an
XML, you need a way to tell the stored procedure which
changes you made to the order (for example, if you added and
deleted a row line), so sending a simple XML with the order
information won't be enough. You need to send out of band
information that describes the operations done in the Order,
and probably the previous values for the modified rows. This
starts to look like a diffgram ;).
Having a stored procedure that receives a diffgram with the
Order and applies business logic and updates the database
looks like a good solution, and it seems it could fit
Barrys' requirements. The only issue is that you need a
diffgram-friendly platform (or another XML standard that can
be used to exchange diffgrams that is supported in multiple
platforms) if you want to have an easy way to work with
those stored procedures from a different platform.