Developers can easily overlook this without good documentation and governance.

mutually validating-65

We needed to ensure that we were not sending emails to or storing personal details about people who had opted out. To ensure we met this requirement we needed to do two things: This is the most obvious solution.

It's simple to implement - just add an opted out column to the demographic table.

Then create a check constraint to ensure that the other columns are null when this is set. The biggest of these is ensuring that all queries have the correct where clause.

One with the demographic columns where the opt out flag isn't set. Provided no one has direct access to the underlying table this can work well.

Developers must still take care to ensure they use the views rather than the base table.

This method also has system maintenance implications.

You must update the check constraint when adding new columns to the table.

Without this you can accidentally generate mailing lists including opted out emails.

If you're on Oracle Enterprise Edition you can use Virtual Private Database.