Question Most efficient way to save boolean value with history

VentureFree

Well-known member
Joined
Jan 9, 2008
Messages
54
Programming Experience
5-10
I'm making a tool to help some team members process some things more efficiently. Originally it was just for one person, but now several people are using it, and I'm trying to coordinate their efforts so they don't end up working on the same things.

With that in mind all I really need to do is save when something has been handled and by whom. This must be reversible, so something marked as already processed can be changed back to an un-processed state (in case an error is later found). I also would like to have a simple history of who marked and unmarked what over time. To summarize, as the subject line says I need to track a simple boolean value in such a way that I can have a history of how that value has changed and by whom. Here's what I was thinking:

VB.NET:
CREATE TABLE "PROC_TRACKER" (
"PROC_ID"     NUMBER(10, 0) NOT NULL, --Primary Key
"SOURCE_ID"   NUMBER(10, 0) NOT NULL, --ID of the source object being processed
"SOURCE_DATE" DATE NOT NULL,          --Date for which the SOURCE_ID is being processed
"PROC_ON"     DATE NOT NULL,          --The exact date/time that this was marked or unmarked
"PROC_BY"     NUMBER(10, 0) NOT NULL, --ID of the person who marked/unmarked this
"IS_PROC"     NUMBER(1, 0)            --Indicates whether this is considered processed or not
CHECK ("IS_PROC" IN (0,1)) NOT NULL,  --Make sure "IS_PROC" is 0 or 1 (i.e. true/false)
CONSTRAINT "PK_PROC_TRACKER" PRIMARY KEY ("PROC_ID")
)

The SOURCE_ID and SOURCE_DATE are a unique pair of values, and but for wanting the history these would make for good primary keys. But since I do want the history, then instead of inserting once and updating that entry whenever it changes, I figure each toggle of the boolean value will be a separate INSERT and I can get the current state with the following:

VB.NET:
SELECT "SOURCE_ID", "SOURCE_DATE", "IS_PROC", MAX("PROC_ON") AS LAST_PROC
FROM "PROC_TRACKER"
GROUP BY "SOURCE_ID", "SOURCE_DATE", "IS_PROC"

And if I want a history of, say SOURCE_ID = 1 AND SOURCE_DATE = '2011-11-01' I would use:

VB.NET:
SELECT "IS_PROC", "PROC_ON", "PROC_BY"
FROM "PROC_TRACKER"
WHERE "SOURCE_ID" = 1
  AND "SOURCE_DATE" = TO_DATE('2011-11-01', 'YYYY-MM-DD')
ORDER BY "PROC_ON"

Is there a better way to do this? Also, do you think perhaps I should use SOURCE_ID, SOURCE_DATE, PROC_ON, and PROC_BY as the PK instead, or maybe just add a unique constraint for those? Finally, should I use an index, and if so what would work best?
 
Last edited:

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
I dont quite understand if a PROC can be processed by multiple people and each be the latest..i.e. if 5 people have to work on a proc nd each give their TRUE before it becomes usable, then sure have proc_by in the pk but if a PROC stands on its own and is either completed or not regardless of who did it, don't put the proc-by in the pk

Ive been maintaining a banking system that uses a max(effective_date) type situation to record address history, so one client might have 100 historical addresses, and each time we want to know the current row, it's
select * from addresses where client = 1 and effective_date = (select max(effective_date) from addresses where client = 1)

There are a few other things in the where too, but suffice to say for a low number of clients it's quite quick.. For a high number, it's a nuisance. Your select max idea will work in the current context but future fields added to this table will cause a nuisance..
Essentially, storing the latest record in the same table as a historical record is a pain in the ass, especially since few people care about the history (we nearly never query historical data)

Instead I now keep the current and historical data in separate tables with similar structure. I UPDATE the main table. A trigger that fires when the update is taking place, inserts the history i.e. the current values before update, plus the current time, into a history table..

This has massively simplified the work

Also, I'd avoid using number(1,0) for a boolean.. use char(1) isntead, so it can be T or F. Constrain it to these values using a trigger. The reason i advoocate using T or F rather than 0 and 1 is that it's clear which is true and which is false (avoid "magic numbers" when you program), and it gives scope to use other letters in future to mean something else: Processed, Converted, Reprocessed, Upgraded, Whatever.. ;)
 

VentureFree

Well-known member
Joined
Jan 9, 2008
Messages
54
Programming Experience
5-10
Thank you very much. This was a very informative and helpful response. I think I used a "history" table like the one you described on another project and promptly forgot about it. I think I'll use it again here.

Also thanks for the tip about the boolean value. I saw an example that used the Number(1, 0) and just used that ever since, but your method is both more accurate and more flexible, so I think I'll use that going forward.
 
Top Bottom