Need help with data validation..

Hi,

we are refactoring a larger merchandise management system atm. It's a
more complex schema with about 120 relations. In the current system we
have parts of the bussines logic in the DBMS and other parts, that we
had problems implementing in pl/pgsql, in the app itself. Our goal is to
move all an everything into DBMS now, so idealy even someone using a
simple database frontend could use the app. Almost any access is done
via writeable views and stored procs to have a clean API for the
frontend devs. Someone writing a frontend does not have to understand a
complex versioning system, writing to 9 tables, when he just wants to
update some article data.

We found solutions for most things, but there is one thing, I can't
think of a clean solution for. In the old app we validated all incomming
data on app level first, generated error msgs etc to the user and once
the data seems ok at app level, passed it to the DBMS where the
declarative integrity and a bunch of triggers throwed exceptions, in
case there was something going wrong with the frontend. As said, we want
to move anything to DBMS level now. One problem with to current DBMS
side approach was, that once an exception occurred the transaction is
aborted and nothing is validated further. Imagin a user entering address
data in the frontend and submitting an incomplete form -- you want to
show him something like this:

Please select a title.
Please fill the firstname field.
Please fill the lasttname field.
Please fill the street field.
Please...

and not a single:

ERROR: NOT NULL violation for title_id..

so we basically put the validation logic, we had on app level, on top of
the tables using triggers and access functions. The trigger
(pre-)validates the data, generates language dependant error msgs and
returns a single, nicely formated msg back to the frontend, containing
anything it has to complain about. It does not abort the transaction, it
just prevents the modification. Aborting the transaction is now up to
the client, as there may be a situation where an error may not be fatal
in a given context. For example the user has a form to do something with
20 articles, the action fails for one article as the stock count may not
be sufficient, you still want to save the other 19 articles and just
warn the user that the order amount for one of them could not be
incremented further.

So we have two layers of validation, the first one beeing the
`soft-one', checking for common mistakes, we can thing of. It generates
nice error msgs, and the `hard' declarative one below, that still throws
fata exceptions in case something completly unexpected happend.

Now the real problem is with writable views. We can't bind triggers to
that (for whatever reason). Look at http://hannes.imos.net/prob.png for
a simplified case. There are 2 tables representing one user in the
writable v_users view. The fields that musst be filled in the addresses
table depend on the user_group the user is in. A privat person does not
have to fill in the company field for example. How could we create the
`soft-layer' in that case? :/ Sure we could create an access function
like user_data_save() but this has other limitations as pl/pgsql is
strong typed and a function can be overloaded.

How do others do something like this? Are there better ways for the
whole thing?


Thanks in advance