History Tables Design

[Shadis]

Hi,

I have 12 tables Enterprise profile.

I want to keep history of each change (Old and new values).

What is the best method to design history tables.

1- Mirror of each table
2- Save changes in XML format (field , old value and new value)
3- single Table (field , old value and new value)

Is there any other ways ?

--------------------------------
for example:
[ENTERPRISE TABLE]
ENTERPRISE_ID
NAME_EN
CAPITAL

[ENTERPRISE_MANAGERS TABLE]
ENTERPRISE_MANAGERS_ID
ENTERPRISE_ID
MANAGER_ID

[MANAGERS TABLE]
MANAGER_ID
NAME_EN
SALARY
--------------------------------------------------------
I have three modes ADD, EDIT and AUDIT.

The changes will be logged after the edit and AUDIT
--------------------------------------------------------

ENTERPRISE_ID,NMAE_EN,CAPITAL
1,'Company 1', 10000000
MANAGER_ID,NAME_EN,SALARY
1, 'John', 100000
2, 'Mark', 120000

ENTERPRISE_MANAGERS_ID,ENTERPRISE_ID,MANAGER_ID
1,1,1
1,1,2

***********After EDIT***************
ENTERPRISE_ID,NMAE_EN,CAPITAL
1,'Company 1', 50000000
MANAGER_ID,NAME_EN,SALARY
1, 'John', 100000
3, 'SAM', 123000
ENTERPRISE_MANAGERS_ID,ENTERPRISE_ID,MANAGER_ID
1,1,1
1,1,3

*********************My history Page should look like this**********
Field, OLD VALUE, New Value, Action
Capital, 10000000, 50000000, EDIT
Manger Name, Mark, - , Delete
Salary , 120000, - , Delete
Manger Name, -, SAM , ADD
Salary , -, 123000 , ADD

[slimdave]

judging from the output you want, I'd think that one table would do it.

You might consider a composite partitioning scheme for it, with the date of the change being a high-level range partition, and the table being a lower-level list or range partition. It'd be easier to query by time of change and table, and you could drop or archive the older history more easily

[hrishy]

Hi Dave

Can you explain why not simple range partitioning ....why did you sugest composite partitioning.

regards
Hrishy

[Shadis]

Suppose i want to get the Managers of certain year of an enterprise from the history.

This will be complicated operation, since we need to find all added and not deleted managers then find the latest changes of these records.

So i think the extraction of usefull data from this table will be difficult.

Sorry i did not mention this issue on my first post.

[slimdave]

Originally posted by hrishy
Hi Dave

Can you explain why not simple range partitioning ....why did you sugest composite partitioning.

regards
Hrishy

I'm guessing that the queries against the history table would probably specify a date range and a table -- to keep the insert overhead on the history table low you want minimum indexes, and with composite partitioning you could probably get away without indexes on date and table.

[slimdave]

Originally posted by Shadis
Suppose i want to get the Managers of certain year of an enterprise from the history.

This will be complicated operation, since we need to find all added and not deleted managers then find the latest changes of these records.

So i think the extraction of usefull data from this table will be difficult.

Sorry i did not mention this issue on my first post.

Can you give a quick example of how the data would look?

[Shadis]

2002 Managers

1, 'John', 100000
2, 'Mark', 120000

2003 Managers

1, 'John', 100000
3, 'SAM', 123000

2004 Managers
3, 'SAM', 500000