Geek Answers Handbook

Database - Data Versioning (continued)

My original question can be found here , for which I have excellent answers, ideas and tips.

As part of a feasibility study, I began to convert my circuits in order to use my data. In this case, I had some other problem.

In my original question, my example was simple, with no real relational references. In an attempt to save an example of my previous question, I will now continue the "Name" part in another table.

So now my data is getting:

Person
------------------------------------------------
ID                UINT NOT NULL,
NameID            UINT NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL

PersonAudit
------------------------------------------------
ID                UINT NOT NULL,
NameID            UINT NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL,
UserID            UINT NOT NULL,         -- Who
PersonID          UINT NOT NULL,         -- What
AffectedOn        DATE NOT NULL,         -- When
Comment           VARCHAR(500) NOT NULL  -- Why

Name
------------------------------------------------
ID                UINT NOT NULL,
FirstName         VARCHAR(200) NOT NULL,
LastName          VARCHAR(200) NOT NULL,
NickName          VARCHAR(200) NOT NULL

NameAudit
------------------------------------------------
ID                UINT NOT NULL,
FirstName         VARCHAR(200) NOT NULL,
LastName          VARCHAR(200) NOT NULL,
NickName          VARCHAR(200) NOT NULL,
UserID            UINT NOT NULL,         -- Who
NameID            UINT NOT NULL,         -- What
AffectedOn        DATE NOT NULL,         -- When
Comment           VARCHAR(500) NOT NULL  -- Why

In the graphical interface, we could see the following form:

ID            :  89213483
First Name    :  Firsty
Last Name     :  Lasty
Nick Name     :  Nicky
Date of Birth :  January 20th, 2005
Email Address :  my.email@host.com

Can change:

  • Only for the 'name' part
  • Only for the man part
  • For parts "name" and "person"

'1', NameAudit . , Person PersonAudit .

"2", PersonAudit Person . , Name NameAudit .

"3", .

100 , , . , . , .

, , NameID Person NameAudit ( ).

.

, - ?

+5
2

, " ". , , - , " " ( ) " , SQL Server, . , Morgan Kaufmann Publishers, Inc., -, 1999 ., 504+ xxiii, ISBN 1-55860-436-7. , PDF - cs.arizona.edu. " Allen Relations " - .

, DATE ( , , Oracle). SQL, , TIMESTAMP . DATE, , , .

, , -, , , . , ; , , . , "" ( Snodgrass) , :

Start time        -- When this set of values became valid
End time          -- When this set of values became invalid
PersonID          -- Person.ID (or PersonAudit.ID) for Person data
NameID            -- Name.ID (or NameAudit.ID) for Name data
DOB               -- Date of Birth recorded while data was valid
Email             -- Email address recorded while data was valid
FirstName         -- FirstName recorded while data was valid
LastName          -- LastName recorded while data was valid
NickName          -- NickName recorded while data was valid

, Person.ID ; Name.ID. , .

" " " ", ( ). , , , . ​​ , , ( XYZAudit , ?).

. , , .

, :

, "Asserted Versioning" . "" : , " Tom Johnston Randall Weiss. AssertedVersioning.com. : .

SQL 2011 (ISO/IEC 9075: 2011, ). . , , TemporalData.com, , .

+6

ID .

.

, .

:

`Change`

1
2
3
4
5
6

`NameAudit`

1 - created as John Smith
5 - changed to James Smith

`PersonAudit`

1 - created as born on `01.01.1980` in `Seattle, WA`
2 - changed DOB to '01.01.1980`
3 - changed DOB to '02.01.1980`
4 - changed DOB to '02.01.1980`
6 - changes POB to `Washington, DC`

:

SELECT  c.id,
        (
        SELECT   MAX(id)
        FROM     NameAudit na
        WHER     na.id <= c.id
        ) as nameVersion,
        (
        SELECT   MAX(id)
        FROM     PersonAudit pa
        WHER     pa.id <= c.id
        ) as personVersion,
        na.*,
        pa.*
FROM    change c
JOIN    NameAudit na
ON      na.id = nameVersion
JOIN    PersonAudit pa
ON      pa.id = nameVersion
WHERE   change_id BETWEEN 1 AND 6
0

More articles:


All Articles