Hello, I've been reading for a while about temporal database and the main thing I understood was: it's hard. I would like to present the way I think of implementing temporal database (or version database) and I'll be happy to hear what you think or how you will go about it.
Let's start with the requirements: I have a project which evolve around "organizations and processes". The main issue is to model the process an organization have in pretty simple way. Each process has actions. Each aciton has a "sender" object and "receiver" object as well as "aciton type". Also each action has properties, for simplicity let's say each property has a type and a value. In the database I have few tables, just to name a few: Object, Process, Action, AcitonType, ActionProperty, PropertyType. I need to keep track of any change to the database and be able to recreate the exact state of the database in past time. It means track every insert, update and delete in the database and not change the history, but preserve it. The database is small and there will be not many changes, please let's not get into the system side of this issue just the implementation. The way I'll go about it: I'm going to add to every table in the database (and I mean all the tables) column which I'll call "ChangeVersion" and "IsDelete". I'll have a single sequence (not implying oracle only) which will generate unique sequential raising number for every change in the database. INSERT will just put the next number in the sequence to the "ChangeVersion" column. UPDATE will actually be implemented as INSERT which again put the next number in the sequence to "ChangeVersion". This way I always leave the history intact. DELETE will actually be implemented as INSERT which will create row with the ID and "ChangeVersion" values only and set "IsDelete" column to true. Everytime a number is asked from the sequence I'll add a row to "Versions" table which will have the following columns: Version - The number of the current version which will be stamped to the changed row. Date - The current date and time This table binds each version to specific time, but does not bind the versioning system to specific clock. Even if the clock will be moved the version system will still work right. The way I'll select data: I'll always work on a specific version which I'll call "ContextVersion". To select information from the database I'll select the most high "ChangeVersion" row which is smaller than or equals to "ContextVersion". What I'm willing to give up: I'm willing to not have database constrains. I'll have reference check if they will be manageable to implement in the database, but any other integrity logic will be done by the application. The question: What do you think about the implementation idea? How can I take advantage of NHibernate (or Java Hibernate) to simplify the implementation. Thank you very much, Ido Ran. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
