Query to get all revisions of an object graph

Question

I'm implementing an audit log on a database, so everything has a CreatedAt and a RemovedAt column. Now I want to be able to list all revisions of an object graph but the best way I can think of for this is to use unions. I need to get every unique CreatedAt and RemovedAt id.

If I'm getting a list of countries with provinces the union looks like this:

SELECT c.CreatedAt AS RevisionId from Countries as c where localId=@Country
UNION
SELECT p.CreatedAt AS RevisionId from Provinces as p 
INNER JOIN Countries as c ON p.CountryId=c.LocalId AND c.LocalId = @Country
UNION
SELECT c.RemovedAt AS RevisionId from Countries as c where localId=@Country
UNION
SELECT p.RemovedAt AS RevisionId from Provinces as p 
INNER JOIN Countries as c ON p.CountryId=c.LocalId AND c.LocalId = @Country

For more complicated queries this could get quite complicated and possibly perform very poorly so I wanted to see if anyone could think of a better approach. This is in MSSQL Server.

I need them all in a single list because this is being used in a from clause and the real data comes from joining on this.

Answer 1

An alternative would be to create an audit log that might look like this:

AuditLog table
    EntityName varchar(2000),
    Action varchar(255),
    EntityId int,
    OccuranceDate datetime

where EntityName is the name of the table (eg: Contries, Provinces), the Action is the audit action (eg: Created, Removed etc) and the EntityId is the primary key of the modified row in the original table.

The table would need to be kept synchronized on each action performed to the tables. There are a couple of ways to do this:

1) Make triggers on each table that will add rows to AuditTable
2) From your application add rows in AuditTable each time a change is made to the repectivetables

Using this solution is very simple to get a list of logs in audit.

If you need to get columns from original table is also possible using joins like this:

select *
from 
    Contries C 
    join AuditLog L on C.Id = L.EntityId and EntityName = 'Contries'

Answer 2

You could probably do it with a cross join and coalesce, but the union is probably still better from a performance standpoint. You can try testing each though.

SELECT
     COALESCE(C.CreatedAt, P.CreatedAt)
FROM
     dbo.Countries C
FULL OUTER JOIN dbo.Provinces P ON
     1 = 0
WHERE
     C.LocalID = @Country OR
     P.LocalID = @Country