How do you deal with dead people?

[keelbolt]

I have built a small database for a charity to help them manage their growing membership records. Each record has a 'status' field where membership can be set to 'Active', 'Inactive', or 'Deceased'. Having argued that the charity should not delete 'Deceased' records because those records may be needed even though the person is no longer breathing or donating, the charity wisely asks that the database "should not show deceased people", but we should keep their records.

How do other database designers deal with dead people?

i could write a script that performs a find all records but omits 'Deceased', but as the database has many scripts using a 'find all', this will make the database cumbersome. Ideally I want the database by default to include all records except those marked 'Deceased', so that I don't have to run a script every time a find or display records is required.

Another option I suppose might be to have a table of 'deceased' members, and move their records to that table, excluding them from the main database.

This is really a organisational issue. It cannot be unique (any database with records of individuals will eventually face this problem because of the universality of the grim reaper), and I'd love to know how other people design around this issue.

[JerrySalem]

I similar situations (Medical, Membership, School Databases etc.) I have dealt with dead people just as you have.

Adding it to the status field.

In some instances, I have had a separate field for a deceased flag, but in the end, it was easier to put it in status. Being dead overrides your active/inactive status.

You could move the deceased records to another table (similar to archiving) but I am sure that would throw off a report or (worse) the financials.

Jerry

[Fitch]

There's no getting around Show All Records, you'll have to go into those scripts and modify them. It's not that big a deal, just Find wildcard in the ID field and add a Set Field(deceased) and an Omit step.

The trick might be in finding all those scripts that need fixing. Ideally you'd use a DDR and BaseElements or Inspector, but a cheap and easy method is to print your scripts to a PDF, which you can then search.

For the Show All Records and Find commands, you can use custom menus to run a script that omits the deceased records.

You might also consider custom formatting to highlight or dim deceased records where you do allow them to be shown.