Flagging Archived Data

[kd2017]

Is this a good idea or bad idea:

You have a chain of related tables. At the "root" table you have a bool flag [is_archived]. If a [root] record is archived then all the related children and grandchildren and so on would also be considered archived.

The idea is the child tables also have an [is_archived] flag, and these flags are part of the foreign key relationships. The referential integrity is set up to 'cascade updated related fields'

• So if you tick a root record as archived then the db will automatically update all the related records at each child, grandchild, etc, as archived. this could be thousands of records per each root record. No need for (multiple) update queries
• Queries against the child tables can now be simplified, you don't have to join related tables or do lookups to figure out what records don't belong to archived root records. I assume this could potentially improve performance substantially
• this is de-normalizing
• you cant archive just parts of the data within a root record's family or related data, it's all or nothing

Thoughts?

[Micron]

IMO a boolean field is not of much use for this. I'd use a date field. If it's null, it's current. Anything with a date would not be returned in a query if you create it that way. As for the related records, there'd be no parent record in the main data returned by the query (if you set criteria to be null), therefore no child records would be returned anyway. Having archive field in the child tables is redundant and only adds an unnecessary level of complexity.
Plus, a date field tells you when the record was archived as well. A checkbox doesn't do that.

[pbaldy]

Also, this would preclude the possibility of a subwidget being used for more than one widget. I don't know if that fits your actual scenario, but wanted to mention it.

[kd2017]

... As for the related records, there'd be no parent record in the main data returned by the query (if you set criteria to be null), therefore no child records would be returned anyway.

My point is it would now be possible to run aggregate queries on child tables without needing to first join parent tables, or an entire lineage of tables, to filter out archived data. (apologies, I probably should have mentioned aggregate queries in the op)

Let's imagine that a child table might have upwards of a million records. Is it faster for ms access to filter by joining (multiple) tables or filter by a flag in the table itself (or "is null" if appropriate)? I don't know, I should probably put together some tests.

Having archive field in the child tables is redundant

agreed!

and only adds an unnecessary level of complexity.

maybe not entirely unnecessary if it means a performance boost?

Also, this would preclude the possibility of a subwidget being used for more than one widget. I don't know if that fits your actual scenario, but wanted to mention it.

That's a great point to keep in mind, thank you. It goes along with my final point that it's all or nothing up and down the chain of related tables.

[kd2017]

...only adds an unnecessary level of complexity...

But does it really? with 'cascade update related fields' the db engine takes care of everything. After you set up the relationship you can forget about it... i think. lol

It certainly adds bloat with extra indexes and duplicated data.

[Minty]

I haven't tested this, but I suspect a join is very efficient.

[Micron]

You're right - it's denormalized - in the sense that you have repeating data. Whether or not it is a good idea or not I can't say but as noted, it's a bad idea if an archived sub is needed on another parent. I can say that I've never needed to be concerned with running queries on child tables (as much as I can recall) so there's that. I am also of the understanding that joins are very efficient. It seems to me the only upside to this is simpler queries, which in your case it seems there's nothing to gain with respect to performance, but only in not having to take the time to do it right (if I can put it that way). I guess time will tell.

[CJ_London]

or filter by a flag in the table itself (or "is null" if appropriate)?

null or not null would be better because you can exclude nulls from an index - so the index is shorter and faster because the logic is it is in the index or it isn’t.

Using a yes/no field it will be populated with 0’s and -1’s so even tho’ indexing is fast the standard recommendation is little point in applying indexes to fields with a limited range of values - it would effectively be the equivalent of a sequential search

[kd2017]

Interesting point about nulls and indexes.

Ive read that there isn't much to gain from indexing bools but I have seen reports on massive performance gains from doing it anyway. It just depends on the circumstances it seems. https://stackoverflow.com/questions/...-boolean-field

This might be beside the point but for the record I use faux bool fields in access per Allen Browne recommendations. That's an integer fields with a validation rule of "-1 or 0"

[kd2017]

Update: I threw together some test data of about a million rows. From my testing the performance difference is minimal to almost non existent between filtering by joins vs filtering by field within table. I also am not seeing a performance difference between True/False vs Is Null/ Is Not Null.

[Micron]

From my testing the performance difference is minimal to almost non existent between filtering by joins vs filtering by field

So where is the gain by not normalizing and instead, using cascade updates vs queries with tables you otherwise don't need - or is there any? While this may work for your case, the fact that archived sub data cannot be used by a different parent (or grandparent as the case may be) makes it a non-starter for me. Imagine a scenario where a sub component is no longer specific to a particular parent component because a new parent makes it so, yet the child has been archived. Or the parent is redesigned to the point where the updated parent becomes a new part and the old version info is archived and the information kept. Now the link is broken between the archived version and its child because the parent is archived and the child is not.

I think that's all I can offer as commentary to the original question.

[kd2017]

So where is the gain by not normalizing and instead, using cascade updates vs queries with tables you otherwise don't need - or is there any?

From post 10, as far as I can tell if there's any performance gain at all it's negligible

(I'm not trying to advocate for this method, was just exploring an idea)