[AccessD] Stored Procedure not producing results

artful at rogers.com artful at rogers.com
Wed Nov 22 09:18:44 CST 2006


I agree with you on the usefulness of triggers, but there are problems (surmountable). Consider a database (or collection of databases) whose size grows by a TB per year. This is going to be a huge problem, literally. Chances are that you won't be able to support the OLTP and OLAP instances on a single server, unless you have a SAN and very fancy hardware. This is going to cause RI problems (on MS-SQL at least; Oracle has table-spaces and can easily work around this issue), and also trigger complexities (fire a trigger on instance X and have it do something to instance Y which resides on another server in another city). It can be done, but it is non-trivial.

And I am "extremely" flattered that you noticed. 

----- Original Message ----
From: Shamil Salakhetdinov <shamil at users.mns.ru>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Wednesday, November 22, 2006 9:49:33 AM
Subject: Re: [AccessD] Stored Procedure not producing results

<<<
As anyone who has followed my threads must
know by now, I tend toward extremism.
>>>
That's so true Arthur! :) (Just kidding of course)

<<<
That said, I suggest that you design PITA in,
just in case it later becomes needed.
>>>
Arthur, these days I'm "biased" toward simple designs and MS SQL triggers
and a parallel OLAP database could be such relatively simple extendable
(plugged/unplugged anytime) solution. I mean I'd just use such triggers to
"replicate" deleted/inserted/updated rows into parallel OLAP database with
tables' structure as you describe i.e. with BeginData and EndDate added.
(And I'd do that only for the tables' customers wanted to have your PITA
enabled with.)
Then all PITA enabled forms/reports/SPs/... will use this parallel OLAP
database together with actual OLTP one if needed...

Do I miss some more effective solutions of this practical task? As you wrote
harddisk memory is cheap these days - I agree with that of course - that's
also a point, which makes rather simple solution with "parallel versioned
data OLAP database" inexpensive...

<<<
"We need to be able to roll back the database to any given PIT without
actually having to do a restore. We just want to specify a date (Jan 1 2004)
and without changing a single line of code, run the reports.
>>> 
You mean "virtual rollback" of course? IOW a "point in time aware"
view/SP/UDF?
 
--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Wednesday, November 22, 2006 5:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Stored Procedure not producing results

I have no way to verify whether you mused on this subject or not, but since
you asked, I will provide a thumbnail sketch:

0. You could do it with triggers, but that would not be my first choice.
1. every table will contain two columns called BeginDate and EndDate.
2. any inserted row will have a NOT NULL value in BeginDate, not necessarily
equivalent to today (otherwise you couldn't insert a row that takes effect
on January 1, 2007).
3. any row  without an EndDate is assumed to be effective now.
4. any update to an existing row copies its data to a new row, fills in the
EndDate column on the existing row with GetDate(), and supplies GetDate() as
the BeginDate on the new row.
5. Any "deleted" row is not actually deleted. Rather, it is preserved, but
its EndDate column is updpated to GetDate().

That's a quick thumbnail sketch of PITA. I may have left out a quirk or two
in the sketch, but that's the basic idea. Nothing is destroyed. Everything
is kept, and its time-span is demarcated by its BeginDate and EndDate
columns. 

Taking a practical example, I just switched doctors yesterday. This involves
several operations.

1. The "family physician" has changed, but do not destroy the previous data.
Therefore insert a new row whose BeginDate is yesterday, and update the
previous row, changing its EndDate to yesterday.
2. Agree that all previous medical data concerning me shall be forwarded to
the new physician. (Called Consent, in the medical lingo.)
3. Step two can be further refined, as in "release data about conditions x,
y and z, but not t, u and v" to my new physician.

At the end of the day, for various important reasons, not the least of which
is litigation, we must be able to determine the state of your particular
record(s) in the database as of January 1, 2004. Who was your physician?
What rights did she have to your prior data? What tests were performed upon
you, and on what dates, and with what results and with what follow-ups?

I chose this example a) because I have been there and written that, and b)
because it illustrates the problem vividly. We could easily substitute
lawyers for the physicians, or dentists, or any professional, including
ourselves, software developers. Consider a project as a metaphorical person
in the above. In 2001, the practitioner list comprised Fuller, Colby and
Salakhetdinov. In 2002, it comprised Hindman, Carbonell, Colby and Lawrence.
In the medical schema, one could easily have several practitioners, one for
heart, one for ENT (ear, nose, throat), one for gynecology, etc. And the
same holds true for a software client -- she employs an accounting expert, a
CRM expert, an Excel macro wizard, an Access expert, and so on.

That was a bit of a sidetrack, but remains on point, I think. The point
being, "We need to be able to roll back the database to any given PIT
without actually having to do a restore. We just want to specify a date (Jan
1 2004) and without changing a single line of code, run the reports.

That is the point of PITA.

It must be designed in from the outset. Not every app needs PITA, but it
becomes very difficult to add it after the fact. As anyone who has followed
my threads must know by now, I tend toward extremism. That said, I suggest
that you design PITA in, just in case it later becomes needed. In that case,
you look like a genius. And if the requirement never emerges, you wasted
some very cheap disk space. 

Arthur

<<< tail skipped>>>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com







More information about the AccessD mailing list