Storing Actual history and database history

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Storing Actual history and database history

Author

Topic

michael.appleton
Posting Yak Master

160 Posts

Posted - 2009-12-11 : 06:54:07

Hello,
I'm creating a table to track real changes of an object and also our knowledge of an object against time. For example, it could be some statistics about a town. The kind of design I've come up with so far is:



--These will be constants that will never change since the town is created in the database
CREATE TABLE TownSeeds(
	Id int Identity(1,1) CONSTRAINT PK_TownSeeds Primary Key,
	EntryDate DateTime NOT NULL DEFAULT Current_TimeStamp
)



--stores changing attributes of the town
CREATE TABLE Towns(
	Id int CONSTRAINT FK_TownsChanges_TownSeeds FOREIGN KEY REFERENCES dbo.TownSeeds(Id),
	TownName varchar(255) NOT NULL,
	Population float,
	
	--other attributes...
	
	--actual history of the town
	ChangeDate DateTime NOT NULL,
	NextChangeDate DateTime NOT NULL,	--created to make queries easier
	
	--stores our history of knowledge of the town
	UpdateDate datetime NOT NULL,
	NextUpdateDate datetime NOT NULL,
	CONSTRAINT PK_Towns Primary Key(Id,UpdateDate)


)

Here is some example data being inserted:



--Create town with two points in history where population changes
INSERT INTO TownSeeds VALUES(DEFAULT,DEFAULT)

DECLARE @Town1Id int
SET @Town1Id=Scope_Identity()
DECLARE @EntryDate datetime
SET @EntryDate=(SELECT EntryDate FROM TownSeeds WHERE Id=@Town1Id)

INSERT INTO Towns
SELECT
	Id=@Town1Id,
	TownName='test town',
	Population=5000,
	ChangeDate='19050101',
	NextChangeDate='19100101',
	
	UpdateDate=@EntryDate,
	NextUpdateDate='99991231'
		

INSERT INTO Towns
SELECT
	Id=@Town1Id,
	TownName='test town',
	Population=7000,
	ChangeDate='19100101',
	NextChangeDate='99991231',
	
	UpdateDate=@EntryDate,
	NextUpdateDate='99991231'
	

--Better research shows that the actual population was 5500 in 1905

DECLARE @UpdateDate datetime
SET @UpdateDate=CURRENT_TIMESTAMP

UPDATE Towns
SET NextUpdateDate=@UpdateDate
WHERE Id=@Town1Id AND UpdateDate=@EntryDate


INSERT INTO Towns
SELECT
	Id=@Town1Id,
	TownName='test town',
	Population=5500,
	ChangeDate='19050101',
	NextChangeDate='19100101',
	
	UpdateDate=@UpdateDate,
	NextUpdateDate='99991231'

I'm a little concerned it might be hard to apply constraints to this data with this added complexity.

I'd really appreciate some feedback on this design. I'm guessing this is a fairly common requirement so I'm hoping someone here will have some helpful feedback from their experience implementing something similar.

Thanks,
Michael

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-12-11 : 07:45:05

how about?

town
id

towndata1
fkid
population
start date
end date
input by
authorised by

towndata2
fkid
name
start date
end date
input by
authorised by

etc...

basically you have a one=>many table describing the attributes you need to record about a town. the many bit is to track data chances over time. the input/auth bit is to allow for changes to be proposed/approved in case it matters.

the end-date can be 9999-12-31 or whatever date can be used to represent "infinity or forever". if an attribtue needs changing then the end date is "made real" and the start date for the next record is the "end date + 1" with the next end ate back up to 9999-12-31. that way you always have a current record and also you have no period of time where an atribute has no value/no duplicate values. care needs to be ataken where multiple changes are made on the same day. intermediate ones may need to be deleted.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2009-12-14 : 05:34:48

Interesting idea, I'm guessing you mean one table for each changing attribute. The issue with that is I have many more changing attributes than in the example, so might need 10 tables or more for just towns, never mind other objects that I need to record history of.

Also, this is only one dimension of changes, the internal update of data, rather than the update of the real object I.e when the population actually changes of a town as opposed to when it is changed in the database. It is important for us to always be able to see our view from any point in time in the past and equally important to record the history of the real object.

I do a similar thing with the date thing, except I don't make a new entry have a start date end date+1, I make a new entry have the same start date as the last entry's end date, and just query like this:


DECLARE @Date datetime
SET @date=current_timestamp --set to required date

SELECT
	T.*
FROM
	Towns T
WHERE
	T.UpdateDate<=@date
	AND
	T.NextUpdateDate>@date

Anyway, thanks for the suggestion, I appreciate any feedback, even if I disagree it makes me more sure about my original idea! If anyone else has any other suggestions or can see any potential problems in my solution, please add to the thread.
Mike

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-12-14 : 08:42:41

" you mean one table for each changing attribute" yup., especially for the requirement "and equally important to record the history of the real object."

re "same start date as the last entry's end date" grand strategy, as long as nobody is in any doubt which record is active/current on the changeover date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources