Historical Records split over multiple tables

[cbeggan]

We are rebuilding the functionality of a mainframe application on client/server application with relational database.

The mainframe application provided 'history' functionality on the majority of it's screens. 'History' brought the user back to the previous version of the data and highlighted the fields which differed between previous and
current data. The timestamp of the change to the data was displayed. This was quite simple as all of the data displayed on the screen was stored in one place on the mainframe database with a single timestamp associated with
all of the data.

We need to provide similar 'history' functionality in the client / server application. However, the relational data model does not hold all data for each screen on a single table. Each table has it's own timestamp. (Each
table also has an equivalent history table which stores history records.)
This raises the question: what is the best way to present history to users when there is more than one data source and more that one timestamp relevant to the data on screen?

In some cases, we could force all tables represented on a particular screen to have the same timestamp by updating all tables relevant to the screen at the same time even if data has not actually changed on all tables. I have
a concern about the volume of history data this will generate.

I need to define an approach for the presentation of 'history' to users. Has anyone faced this issue? How did you present history information in such circumstances?


------
Dublin, Ireland.

 

[SQLSister]

This is what I would do. Store the historical data in a table which has the same id as the orginal data. Use a datefield set to getdate as the default value to get the timestamp of the data change.

To grab it all at once do a union query

Select field1, field 2 from table1 where ID = @ID
Union
Select Field1, Field2 from HistoryTAble where ID = @ID

Remember, never use select * in a union query, if someone changes just one of thet ables involved, select * will break the query because each part of the union query must have the same number of fields.

Questions about posting. See faq183-874

 

[cbeggan]

Sorry, to clarify, the question is asking about the 'presentation' of historical data from many tables, not the retrieval of same. The issue is about typical or best practice method for presentation of the data on the screen where it may not all have been updated at the same time. To be fair, its more of an application design question than a SQL programming one.

------
Dublin, Ireland.

 

[aalmeida]

It seems to me that your users are the ones to answer that quention, because at this point we can not say the relevance of even showing it to the users.
Basicaly your problem here definition of your requirements first.

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"