Is there a way to "hide" records at the SQL level so that large groups of records can be partitioned into different sets only visible or usable by particular users?
For example, suppose that instead of deleting records, I simply want to hide them (to improve performance or that they are not deleted or seen by an administrator in a sort of history of transactions, etc.). So maybe there is a field in each record called "mask".
[The mask field could be an actual integer used as a bit-mask. But that would not be indexable such that a bitmasking clause in an SQL query would result in increased perfomance. Part of the objective would be to make queries more efficient by excluding large sets of records that do not match the desired mask.]
When a record is "deleted," this mask field is changed so that SQL queries can quickly and efficiently exclude these records from the results.
This will work if all records are in a single partition. However, if you want the records to be in multiple partitions, the behavior should look more like a bitmask in which a single value passed to the SQL query can match more than one partition value.
Is there any clever digital juggling that could be used to create an indexable integer or a text field that can be used as a mask?