@Nelle said:
That being said, I have the same problem in the current project and unfortunately have the 2005, so I did some googling and found this thing:
http://www.codeproject.com/KB/architecture/LisRecordVersioning.aspx
Its Option 2. works nice and so farn and i haven't noticed any performance issues.
No, it's not Option 2, since it involves another table. And IMO it sucks completely. It's by far worse than the naive version of Option 2 where every table has a few additional columns.
Here's why:
a) performance
let's look at a typical bread-and-butter query:
select * from Blog join Audit on Blog.Id = Audit.Id where IsActive=1 and UserId='moron'
So where is the problem, you may ask. Both tables are indexed, aren't they?
Turns out that to find a single record, the database has to loop through all versions of that record (really, no way around that) and join them to the audit table. What would otherwise be the most simple indexed single-row access becomes by far more expensive, though you might not notice in small databases.
b) joins
let's look at a query that returns for all blogs the number of comments
select a1.PermanentRecordId, Blog.title, count(*)
from Blog
join Audit a1 on Blog.Id = Audit.Id
join Comment on Comment.PermanentRecordId=a1.PermanentRecordId
join Audit a2 on Comment.Id=a2.Id
where a1.IsActive=1
and a2.IsActive=1
group by a1.PermanentRecordId, Blog.title
now compare that to the naive version of Option 2:
select Blog.Id, Blog.title, count(*)
from Blog
join Comment on Blog.Id = Comment.BlogId
where Blog.IsActive=1
and Comment.IsActive=1
group by Blog.Id, Blog.title.
or Option 1,3,4:
select Blog.Id, Blog.title, count(*)
from Blog
join Comment on Blog.Id = Comment.BlogId
group by Blog.Id, Blog.title.
and it's getting worse with every additional table, subquery etc.
c) complex update statements
we see on the website you've referenced how complicated inserting, updating or deleting a single record has become.
Now consider the following statement:
update order set foobar = (select count(*) from orderline where orderid=order.id and foo='bar')
where id in (select orderid from orderinfo where bar = 'foo');
Turns out you can't easily do that in that bastardized option 2 model. You'll have to loop through all records and process them one-by-one.
d) impossible to implement with triggers
Options 1,3,4 can be easily implemented with triggers, so the rest of the program does not have to care about versioning at all. The naive version of Option 2 requires a bit more work, but at least you use a trigger that creates a copy when you do update statements.
e) surrogate keys
Some people like them, some don't, but anyway, the given model doesn't allow the use of natural keys at all. No choice.
f) no tool support
Forget visual query builders, ERD reengineering etc.
g) referential integrity
Forget referential integrity constraints. Since all tables technically reference the audit table, referential integrity constraints become worthless.
Conclusion: by implementing that model, you throw away most advantages an RDBMS has to offer. You might as well go back to ISAM files or similar pre-relelational data storage options.