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