nidhis (11/12/2010)

---

Hi,

I have to design a module in an existing system wherein I need to create versioning in the database for a workflow.

I have thought of two options:

1. When the user wishes to modify the data which is in 'Approved' state after a workflow is complete, then create a copy of ALL the data with a new Version Id and status as 'Work in Progress'.

Advantage:

1. historic data will be present in teh same table.

2. not much code change in the UI.

3. Addition of just a filtering will be required in existing reports procedures (apprx50 reports).

Concern Area:

1. Copying the data would be costly on the click of a button because there are approx 70 tables containing the complete workflow details.

2. Create another table to contain the historic data and the current table(s) have the current 'Approved' record only.

Advantage:

1. A new table will contain historic information so querying in the current tables will not be costly performance wise as only current active records will be saved.

Concern Area:

1. Not too sure how I would create historic tables for approx 50 tables. 🙁

Does anyone have a better idea?

Also, what is the most efficient way of copying the data from more than 100 tables so that it doesnt take much time.

Thanks