Home » SQL & PL/SQL » SQL & PL/SQL » Runtime Foreign Keys (for a generic event log)
| Runtime Foreign Keys (for a generic event log) [message #243058] |
Tue, 05 June 2007 18:07  |
gaffonso
Messages: 7
Registered: October 2006
|
Junior Member |
|
|
We've got a need to log various events that occur within our application.
Some of these events, and the data we want to capture for each event, are pretty typical. For example, when someone makes a change to (or creates) a "user" we want to know who/what/when.
Other events are more business-specific. We're a health-care provider, so a business-specific example would be that we want to log events like "the patient just got re-assigned to a new care-owner".
I'm mulling over the structure of an oracle table that will record these events. At first blush this seems extremely simple. Just a table tracking the event_datetime, the event_name, perhaps an event_category, etc., and the FK of the "who" record.
The difficulty comes when we start looking at the "who" part of the equation. In most cases the "who" is represented by different records (in different tables) depending on the event type. A user fk (from the ID column of the User table) in one case, a patient fk (from the ID column of the patient table) in another case, a care-provider fk (from the ID column of the provider table) in a 3rd case.
And there's the rub.
The storage of interesting FK information makes the table-structure itself (not just the tables contents) specific to each event type. The number of columns has to grow with each new event type supported (assuming those events involve an FK), and it also means that each event has a bunch of null entries for the columns that do not apply to any given event record.
So I'm considering a this as a generic solution:
CREATE TABLE EVENT
(
ID NUMBER(19, 0) NOT NULL,
EVENT_DATE TIMESTAMP(6),
EVENT_TYPE NUMBER(19, 0), NOT NULL -- a design-time FK
WHO_FK_TABLE VARCHAR2(128), -- runtime FK: table portion
WHO_FK_COLUMN VARCHAR2(128), -- runtime FK: column portion
WHO_FK_ID NUMBER(19, 0) -- runtime FK: id portion
)
The idea here being that we record the FK for the "who" of the event not with a design-time FK (where we know the table-name and ID-column-name when we construct the table) but with a runtime FK where the table and column of the FK reference are not known until the data is inserted.
Is this an insane way to go about this?
Obviously Oracle's built-in referential integrity features (such as declared FK constraints) go out the window. I can live with that. Particularly since I may be able to get RI back with a runtime-fk-aware trigger.
And doing joins based on this kind of FK is going to be, er, unusual. But with a little runtime code generation (the EXECUTE statement) it doesn't seem like will be that bad.
Some googling for info on this idea turns up nil. So maybe my keyword choices are bad. Or maybe this is such a bad idea that it merits no discussion. I hope it's the former. 
I'd love your input on this and/or pointers to forums/posts/blogs/papers, etc. that have discussed the idea.
Thanks much!
- Gary
|
|
|
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243067 is a reply to message #243059] |
Tue, 05 June 2007 22:13  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It's a tough requirement, so you're unlikely to find a truly elegant solution.
I would support following @DreamzZ links. I haven't followed them myself, and I'm not knowlegable in those areas, but it would be interesting to know whether it will give you the two-way traceability you seem to be after. Ask yourself: how badly do you want two-way automated traceability, and at what cost? Is it adequate to just store details of the triggering row as text in the log message?
A generic design like yours is good for one-way: the event generates enough information to log a meaningful message. It is even human-traceable to a degree, but writing code to join the log back to the source row is complex and ugly. This makes it sub-optimal for two-way traceability.
Having a more specific solution involving a separate structure for each event type (allowing you to have different keys) gives you much better traceability, but at a cost in elegance (of the logging - not querying it afterwards) and development overhead. A specific solution would be much more palatable if there was an over-arching package that built the specific structures from a general pattern. I wonder if this is how fine-grain auditing works?
Another option for a roll-your-own generic solution is to add a "meta data key" column to every table. Then you have an additional table that captures a master list of the meta-data-keys (one key value = one row in a table), and another that captures events triggered by that key (row). This is similar to your proposal except the problem of composite primary keys and data-types is avoided.
Finally, this type of thing is reasonably common in Data Warehousing circles to track data quality issues in the ETL. You might expand your searches to encompass those types of applications.
Ross Leishman
|
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243272 is a reply to message #243059] |
Wed, 06 June 2007 12:59 |
gaffonso
Messages: 7
Registered: October 2006
|
Junior Member |
|
|
Good suggestion and thanks for the links.
Unfortunately for us, this won't work in all cases. Some of the information recorded about an event (it's type, for example) can't be determined just by triggering on a query or column-change.
Patient re-assignment is a good example. There are several reasons that the provider FK associated with a patient may change (their provider retired, they requested re-assigment, etc) but the query to implement the change looks the same to oracle (a simple change in the fk).
Having Oracle control the point-of-decision about what events should be generated (and what data they should contain) limits the information we can use to trigger the event and the amount of information we can record.
In short, we want to use Oracle to *store* a generic event, but we don't want to use Oracle to determine the who/what/when of that event.
Thanks for your input, though!
- Gary
|
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243276 is a reply to message #243272] |
Wed, 06 June 2007 13:15 |
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
|
Senior Member |
|
|
| Quote: | Some of the information recorded about an event (it's type, for example) can't be determined just by triggering on a query or column-change
|
You means that who perform Dml on which table and what did he do??
| Quote: | Patient re-assignment is a good example. There are several reasons that the provider FK associated with a patient may change (their provider retired, they requested re-assigment, etc) but the query to implement the change looks the same to oracle (a simple change in the fk).
|
can you provide more information about this and your expected output?
| Quote: | In short, we want to use Oracle to *store* a generic event, but we don't want to use Oracle to determine the who/what/when of that event.
|
confusing can you please specify more information.
|
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243287 is a reply to message #243276] |
Wed, 06 June 2007 15:20 |
gaffonso
Messages: 7
Registered: October 2006
|
Junior Member |
|
|
| DreamzZ wrote on Wed, 06 June 2007 11:15 | You means that who perform Dml on which table and what did he do??
|
From the perspective of Oracle, the user-account through which the queries are run is always the same (it's the user-account the web-application uses for it's db connection). So within Oracle there's no way to use the Oracle-user-account associated with a query to determine what type of action should be logged.
So, yes, the "who" of the DML, as far as Oracle is concerned, is always the same.
But in the webapp there are separate webapp user accounts. It's just that all the queries generated by users doing activities with those webapp user accounts eventually hit oracle through a single Oracle user account.
| Quote: | can you provide more information about this and your expected output?
|
Let's say a patient is reassigned to a different provider. As noted above this could happen for a couple of reasons: the provider is transferred to a different facility, the patient requests a different provider, etc.
By the time the queries hit Oracle, they're just standard update queries. Here are the two examples, both the query that would run to affect the change, and the corresponding logging statement we want generated.
Patient-requested reassignment
UPDATE USER SET PROVIDER_ID=22 WHERE USER_ID=39;
EVENT_DATE: 2007/06/06 @ 7:30a
EVENT_TYPE: Patient-Requested Provider Reassignment
WHO_TRIGGERED: 1234 (ID for patient Jane Doe)
Provider-retiring reassignment
UPDATE USER SET PROVIDER_ID=22 WHERE USER_ID=39;
EVENT_DATE: 2007/06/06 @ 7:30a
EVENT_TYPE: Provider-Retiring Reassignment
WHO_TRIGGERED: 384778 (ID for Dr. Joe Provider)
| Quote: | confusing can you please specify more information.
|
If I understood your post correctly, you're suggesting that we use triggers as the mechanism to trigger the logging of an event.
But with triggers the triggering event is one of the following:
* An INSERT, UPDATE, or DELETE statement on a specific table (or view, in some cases)
* A CREATE, ALTER, or DROP statement on any schema object
* A database startup or instance shutdown
* A specific error message or any error message
* A user logon or logoff
None of those events will contain enough information necessary to generate the individual log entry we need.
The UPDATE statement trigger comes the closest because you can further specify a column when defining the triggering event. But we still only know:
1) An update occurred on a particular table
2) That update affected a particular column
Neither of those pieces of information is enough to construct a log entry that indicates *why* that query was executed and what "who" data to associate with the log entry.
In short, we can't use Oracle to make the decision about when to log or what to log. The information used to make those decisions is contained within the webapp, not within Oracle.
But we would like a generic table structure within which to record application events (not oracle events). And when some of those log entries refer to "who" entities that have corresponding records in our Oracle tables, we're thinking it makes sense to keep an FK reference to their record.
That lets us at least have a shot at doing something like a join back to the patient-table at some point in the future.
- gary
|
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243289 is a reply to message #243287] |
Wed, 06 June 2007 15:26 |
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
|
Senior Member |
|
|
| Quote: | In short, we can't use Oracle to make the decision about when to log or what to log. The information used to make those decisions is contained within the webapp, not within Oracle.
|
If i am right you want to do it through your front end coding (jave or wutelse)
|
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243293 is a reply to message #243289] |
Wed, 06 June 2007 15:33 |
gaffonso
Messages: 7
Registered: October 2006
|
Junior Member |
|
|
| DreamzZ wrote on Wed, 06 June 2007 13:26 | | Quote: | In short, we can't use Oracle to make the decision about when to log or what to log. The information used to make those decisions is contained within the webapp, not within Oracle.
|
If i am right you want to do it through your front end coding (jave or wutelse)
|
You are correct.
But we want to use an Oracle database table to *store* the events. And for some of those event types, we want our log entry to include information about who initiated the event. The "who's" are entities that have corresponding entries in various tables within our Oracle database. In short, the "who's" can be represented by an FK.
The problem is that the FK is different in nature for different logging entries. For some log entries, the FK points to a record in the PROVIDERS table. For other log entries, the FK points to a record in the PATIENT table.
- gary
|
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243295 is a reply to message #243293] |
Wed, 06 June 2007 15:46 |
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
|
Senior Member |
|
|
| Quote: | But we want to use an Oracle database table to *store* the events
|
this is possible, if you create the procedure and then call the procedure through your code for logging.
| Quote: | The problem is that the FK is different in nature for different logging entries. For some log entries, the FK points to a record in the PROVIDERS table. For other log entries, the FK points to a record in the PATIENT table
|
i am not sure about this.
|
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243302 is a reply to message #243295] |
Wed, 06 June 2007 16:21 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I'm a big fan of normalization, but for this requirement, there are disadvantages of having FKs going all over the place because you impose delete restrictions on the parent tables and because you can't preserve the associations in place at the time the audit entry was made. What you may rather want is a snapshot of the way things looked at the time of the event. This means denormalizing these FKs into the event record. As your audit requirements change you don't want to redefine the table structure all the time.
Having a more flat structure give you more flexibility in what to store, but less flexibility in querying it.
I'd consider having something like an event table with maybe a child table to hold details.
I'd also consider having an event_type table where you can flag the event types to log. e.g. login/logoff events may be needed initially but not later. Finally, a history table related to the event_type table could be used to track when logging level changes are made and by who...
Finally - your logging would be through a pkg/proc called by all other code - not direct inserts.
You should also look at http://www.cs.drexel.edu/~dvista/cs680/4.DW.ETL.ppt page 42
[Updated on: Wed, 06 June 2007 16:36] Report message to a moderator |
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243308 is a reply to message #243058] |
Wed, 06 June 2007 16:54 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
hi, so I got two things to suggest:
1) I suggest first we pay attention to andrew's comments. I too am big on data design, but in this case your need is supplemental or ancilary to the data itself. As such, you should not keep a "hard" fk back to the data. This is to say, a design that implements a constraint would eventually prove disasterous because of the limits it would place on the normal operations of your system. Ask youself this question: would I ever need to remove one of the "who" from my system. If so,what does this mean to my audit data? (cascade delete?).
2) may you should think about XML in a clob field or varchar2(4000) field if the data stream is short. If your app has the data to save, simply store it as XML. You would have to do at least these two things:
a) construct a package of code either in the database or in your web app that would construct each WHO key as an xml stream and save it along with its message.
b) craft views in the database to unpack the WHO data into their constituent components, for those situations when you want to join back later.
If I understand, then put colloquially, you have many piles of "stuff". Each pile of stuff has the needed information to point you somewhere, but each pile of stuff also looks different from every other pile of stuff.
Sounds like the nuts and bolts box in my garage. My solution was to stick each pile of nuts and bolts into a plastic ziplocs and drop them into a box. So, I suggest you stick your WHO key into a XML "ziploc" and drop it into a clob, or varchar2(4000), or xmltype on some table.
You should also explore Oracle advanced indexing capabilities for querying your xml data.
Good luck, Kevin.
|
|
|
|
| Re: Runtime Foreign Keys (for a generic event log) [message #243314 is a reply to message #243058] |
Wed, 06 June 2007 18:39 |
gaffonso
Messages: 7
Registered: October 2006
|
Junior Member |
|
|
I did finally run across some significant efforts at attacking this problem space. IBM has a "Common Base Event" technology:
http://www.ibm.com/developerworks/library/specification/ws-cbe/
(A category of CBE is a business-specific-event intended for just the purposes I describe.)
But, holy crap, their data structure and the tools in dealing with that structure are very heavy-weight solutions, far heavier than I want (or can afford) to introduce into our application.
So I'm continuing to cogitate on this, the CBE isn't a solution for us. I really appreciate the input you guys are providing, I think a solution that fits us is gelling.
- gary
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 01 12:31:29 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
