What is the best way to manage index tables on update/deletion of the indexed data?
I have a table containing all kinds of data fora user, i.e. name, address, contact data, company data etc. Key to this table is the user ID.
I also maintain about a dozen index tables matching my queries, like name, email address, company D.U.N.S number, permissions the user has, etc. These index tables contain the user IDs matching the search key as column names, with the column values left empty.
Whenever a user is deleted or updated I have to make sure to update the index tables, i.e. if the permissions of a user changes I have to remove the user ID from the rows matching the permission he no longer has.