artful at rogers.com
artful at rogers.com
Wed Nov 22 09:14:18 CST 2006
It seems that you missed the point, JWC. In a PITA setup, nothing is ever destroyed or overwritten. Thus I can roll back the database (metaphorically) and discover that on January 11, 2004 Dr. JWC had his office at 123 Main Street, but on February 1st of the same year, he moved his offices to 234 Side Street. On the former date, Dr. JWC had x clients, and on the latter date he had y clients.
That is the whole point of PITA.
You do not apply the concept to some tables and not others, in general. The distinction lies on QCDs (quickly changing dimensions) versus SCDs (slowly changing dimensions). To be safe, you apply the "do not destroy anything" to all tables in the database. For example, Yugloslavia and the former USSR became several countries recently. But we need to be able to look up JWC as of September, 1990 and see the data as it existed then. At that time, Sarajevo was in Yugoslavia. Now it is not. I still need to preserve your 1990 address.
Arthur
----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Wednesday, November 22, 2006 9:49:02 AM
Subject: Re: [AccessD] Stored Procedure not producing results
Yes, but there has to be a limit somewhere. Suppose the physician moved.
Do I care what his old address was. It is the same physician. Suppose he
gets a new phone number, fax number, etc. I can see where in some cases you
would want to preserve every piece of data in particular tables, but in
EVERY table?
And in your "family doctor" example, why not simply create a doctor table,
then a FamilyDoctor table that relates family to doctor. The FamilyDoctor
table has a begin / end date. New doctor, new entry in the FamilyDoctor
table. The old FamilyDoctor entry remains and provides the history of who
the family doctor was at any given date. The m-m FamilyDoctor table is just
two pointers (family and doctor) and a begin / end date. MUCH simpler than
preserving the entire doctor record whenever any tiny piece of that doctor
record changed, not to mention updating a pointer to that doctor record in
every family's record when some trivial piece of doctor info changes.
Again, I am not arguing with the concept, simply the scope of application of
the concept.
So IF you agree that you limit the scope to specific tables, how do you
determine and automatically apply the concept to specific tables, and not to
others?
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----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 9:03 AM
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
----- 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 8:27:43 AM
Subject: Re: [AccessD] Stored Procedure not producing results
<<<
So. Today's job is to dig out that PITA article and pitch it to somebody.
>>>
Arthur,
I must say I did "muse" on similar to your PITA ("Point in Time
Architecture") subject somewhere in year 1992 or 1993, that time in FIDO
groups and in Russian...
Am I wrong - wasn't that feature of versioned database data somehow limited
but built-in way implemented in Borland Interbase?
And in MS SQL Server it can be relatively inexpensive way
implemented/simulated using triggers - correct?
--
Shamil
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com