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:
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:
And if I want a history of, say SOURCE_ID = 1 AND SOURCE_DATE = '2011-11-01' I would use:
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?
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: