Re: Applictaion data Logging

On Thursday 12 December 2002 01:35 am, Jesus Sandoval wrote:

> 3) In my design of data logging, I only have one "logging table" for all
> the application, but this logging table has to have the table name and the
> column name of what is being changed, inserted or deteled, like the
> following:
>
> id SERIAL -- Primary key
> tablename VARCHAR(40) -- Table name that changed
> fieldname VARCHAR(40) -- fieldname that changed in the table
> user VARCHAR(16) -- User that made the change
> timedate TIMESTAMP WITHOUT TIME ZONE -- Timestamp of change
> keyvalue TEXT -- The key value of the record that changed
> oldcontent TEXT -- the field's content before the change
> newcontent TEXT -- the field's new content
>
> I'm not sure if the primary key (id SERIAL) is used in the way you
> suggested the serial number???

no. In your example, you're using SERIAL as a dbms-generated object-ID whereas
the "serial" I meant (think of it as a "version-number") is an integer
that gets incremented each time the tuple is modified.

> 4) What utility can have storing the serial number in the data table????

for portability reasons you should do that at the OR-level, i.e. in
your doUpdate(). Again for portability I would suggest not to use
the "SERIAL"-type for "id". The same functionality can be achieved
via an extra table that holds the last valid object-ID. Whenever the mapper
needs to create a new object, it simply increments the number in that
table, sets it as the id in the new object (and the serial resp. version to 1)
and inserts the record (in a transaction, of course). Nice side-effect:
the object id will be unique among _all_ tables which can be used
for other purposes as well.

> Another design that I'm aware, every time somebody updated the tuple, a new
> tuple where written to the table with a timestamp, so if I wanted to know
> the actual value I just get the record with the greatest timestamp,

this design doesn't work well with some dbms (slow joins)

> and if
> I wanted to know the history I can see all the tuples with the same ke
> value (not the primary key, because in this design, the primary key always
> was a SERIAL value).

I see 2 probs with this design:
- there is one logging record for each modified field in a tuple,
i.e. if 20 fields were modified, you generate 20 records!
- the original datatype is lost.

Well, it depends on your demands.

Harald.

(btw.: is this the proper list for this thread? ;-)