Don Y wrote:
Hi,
I wasn't prepared to ask this question, yet :< but
all the talk of stored procedures, etc. suggests
this might be a good time to venture forth...
Humor me: assume I have done the analysis and *know*
this to be correct for my situation :>
I want to embed a good deal of the invariant aspects
of my application *domain* in the databases that
service it -- instead of in the applications riding
*above* them.
Keeping in mind that you've done plenty of analysis, I'd raise the
standard cautionary note that you have to be pretty certain that you're
right about what's invariant if you're going to couple your application
logic very tightly to your data model.
Anyway, the problem I have is how to handle cases
where the "database" needs user confirmation of an
action (!). I.e. it has verified that the data
coming in is legitimate (adheres to all of the rules)
and *can* be stored in the appropriate tables -- BUT,
notices "something" that causes it to wonder if the
user REALLY wants to INSERT this data. The database
would like to alert the user to what it has noticed
and get confirmation from the user (of course, I
mean my *application* wants to do this -- but, based
on observations made *by* the database, itself).
I've been dealing this myself, as it happens, in the context of
importing and validating data (an ETL kind of thing). My answer is to
use temporary tables as a way of staging the data. One of the
advantages of that is that you can wrap up the DDL for the temporary
table inside your code (whether in or out of the database) and thus
insulate that part of the process from other changes in the destination
tables. Then you can fire off whatever rules you like about whether to
alert the user of certain conditions with the data without worrying
about commits. Of course the transaction approach can probably handle
that, too.
Cheers,
Eric
