Thursday, February 14, 2008

beware a Deleted flag on tables

some of our tables have a 'Deleted' flag, and a row cannot be considered to really be in a table unless the Deleted flag = 0. this means your joins will always have something like "t1.Id = t2.Id AND t1.Deleted = 0".

the design goal here was to keep some historical record of changes without the hassle of a history table, but it was really a bad idea since it’s way too easy to forget about when writing queries, and all indexes are irrelevant unless they start with the Deleted bit...

No comments: