Re: Model Driven Architecture, bussiness rules in DB?

On 2006-07-18, Roy Hann <specially@xxxxxxxxxxxxxxxxxxxxx> wrote:
"Emiliano" <emile@xxxxxxxxxxxxxx> wrote in message
news:95623$44bc782f$3ec29fe3$12143@xxxxxxxxxxxxxxxxx
Hey all,

I'm currently involved in the development of an application framework
using model driven architecture. You basically model your domain, the
tool generates a DB schema with foreign keys and all, and out comes a
ready-to-roll web application. The issue of usability (or lack
thereof) of said one-size-fits-all Web-UI aside, all additional
business rules that aren't covered by foreign keys are hand-crafted
using rules and dbprocs. These non-covered bussiness rules are of
course the majority of the lot, covering complex cross-table
constraints, and a rather complex role-and-status-based row-and-columns
authorization schema. This yields loads and loads of non-debuggable
hand-coded stuff in the DB, which kind of contradicts the idea that a
nearly-ready-to-run application yields from a model.

I'm beginning to feel that maybe these fine-grained and complex
controls and access rights really have no bussiness living in the DB,
but could rather be an additional access layer on top of the DB. At
this level it seems to me it would be easier to develop, debug and
unit test that layer.

Thoughts?

Gosh. Where to begin? Can I assume you are using Ingres?

The choice hasn't really been made yet. But the currently
accountable-to-none architect leans towards SQL Server.

If you are using Ingres you have several problems: Ingres doesn't directly
support general constraints (assertions) and it doesn't directly support
transition constraints.

Then, neither does SQL Server (to my knowledge).

Ingres doesn't support deferred constraints of any
kind. There is--famously--no DBP debugger,

Which isn't really a problem, because people around here don't know
how to work one. Several prototypes have been crafted, using Oracle,
SQL Server, and I tested Ingres. I'm not familiar enough with SQL
Server to know how the debugger works, but noone around here is.

and rule tracing is virtually
non-existent. So you are going to have to build all that yourself. If
you figure out how to improvise deferred constraints, let me know.

I'm thinking towards sizeable bribes directed at Karl, maybe?

SQL itself leaves you with some problems that it properly should have solved
for you. For instance when you grant access to a table you should be able
to use a WHERE clause in the grant. In fact you should be able to project
the columns in a GRANT too.

That's been "solved" in our product with a huge tangle of rules, DBPs
and, you knew it would come, queries assembled from varchar fields in
other tables.

And when you access the data to which you have
been granted access SQL should give you a duplicate-free
projection/restriction of the table automatically. The nearest you will get
to that is a grant on a view, but then you will need one view for each
distinct projection. So you are going to have to build all that yourself.
(Be sure to put each user-specific view in the user's own schema. Don't be
tempted to put lots of complex user-identification logic into the view
definitions.)

Ah, that touches on another problem: the app doesn't present itself as
distinct users to the DB. It's a web application, which logs into the
DB as a single user; the current web-app user is provided as a
fill-in-the-blank (query assembly again!) style string. The authz
checking code is convoluted, and CRUD rights are determined based on
the username, application-defined roles that the user belongs to, and
the contents of the individual rows.

So, going back to your question, I don't think the problem with complex,
fine-grained controls is a fundamental problem with the approach. I think
the approach is absolutely spot-on. The problem is that SQL and Ingres fall
far short of what an RDBMS should do, so you end up coding far too much by
hand and then trying to manage it all without any tools!

Right, but given that we can't wait for a product to emerge that does
implement all that, my question remains: would you handle this in the
DB using rules and DBPs? Or insert a code layer between the DB and the
accessing applications (FKs would obviously still apply)?

Apart from that, I do feel that application-specific information (what I
call state-of-the-application data) should be kept in a different schema
from the core business data. Examples of state-of-the-application data
would be things like application configuration data, user-specific lists of
accessible functions within an application, etc. I would even keep
application-controlled locking information in a different schema from the
data. (For example, if you look at slide 20 in
http://www.rationalcommerce.com/resources/constraints.htm, I would nornally
put the orders_locks table in a different schema from the orders table; the
former is about the state of the applications, the latter is about the state
of the business.)

app-specific data lives in the same DB and is already causing us
problems; minor revisions to the appgenerator expect differently "laid
out" app data in the DB, and are thus can't be run against the same
DB. Which in turn forces us to upgrade all applications big-bang style
when a new generator is rolled out. I have been trying to point out
that this is not a feasible situation for operational use, but
optimism abounds.

Finally, I've done a bit of work on something like an application framework
myself, and the problems that you highlight above may not be the most
serious, or at least the seriousness of the problems mentioned above can be
made less important. THE big problem I found, was the lack of a robust,
*integrated* process modelling tool.

You don't say. Workflow is currently all status-based, but users get lost in
the app deciding what to do next. I've been thinking about trying to
integrate Windows Workflow Foundation, and coupling that to the authz
in some way.

Without a clear process model that
gets generated into the application automatically, with decent monitoring
tools so that you know where each work-item is, that *fully defines
processes for reversals and business process error recovery*, everything is
infinitely more complex and unreliable. Add that to the problems you
already noted, and, well, figure it out...

I have begun to. The sight's not necesarily pretty.

--
Emiliano
.

Relevant Pages

  • Re: Need help on how to organize users and objects
    ... Yes Oracle is a bit different than Sql Server, but I think you will get the ... It doesnt become part of any schema until you assign it. ... a new developer started to work. ... need the COMMON database which stores tables with generic data and generic ...
    (microsoft.public.sqlserver.security)
  • Re: 3 Simple Security SQL Statements
    ... In SQL 2005, when you create an object, you specify the schema that the ... Kalen Delaney, SQL Server MVP ... Schema for a database role, that seems like the best setup. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server to .xsd
    ... > output an XSD from an entire database schema, ... > "Val Mazur" wrote: ... >> Do you need to get schema of specific table in a SQL Server? ...
    (microsoft.public.data.ado)
  • Re: How to access SQL Server 2005 with Query Analyzer (SQL 2000)
    ... Query Analyzer does not fully support new SQL Server 2005 features. ... it does not recognize schema names in the Object Browser. ... > SQL Server databases use the concept of schemas. ...
    (microsoft.public.sqlserver.clients)
  • oracle grants/roles v sql server
    ... I want to grant a user read access to all objects in a schema ... In sql server, I would just give the user a db_datareader role ... How is that done in Oracle? ...
    (comp.databases.oracle.server)