I'm trying to develop a small reporting tool (with sqlite backend). I can best describe this tool as a "transaction" ledger. What I'm trying to do is keep track of "transactions" from weekly data extract:
- "new" (or add) - resource is new to my app since my app may not have tracked this resource before as it hasn't been seen via extracts.
- "update" (or hit) - there is a recent use of that resource, update retention period by another week.
- "delete" (or drop) - this item saw no use since last report (optional, but would be nice to have for graphing week-to-week changes in demand for resources).
All I've got is a weekly data extract (pipe-delimited flat file) coming from a legacy archiving/record-management system that I have no control over.
Each line can be distilled to basically this:
resource_id | resource info | customer_id | customer_info
10| Title X | 1 | Bob 11| Another title | 1 | Bob 10| Title X | 2 | Alice
The goal is make it easy to report on resources that haven't seen use for X-months (based on last hit). There is a retention period where resources are kept around for ease of access if they're popular. A resource that hasn't seen use for 18 months is marked for long-term archival elsewhere.
This must be a common problem. Wondering if there a general-purpose algorithm to determine what's new/same/removed between data sets (db vs. latest extract)?