user role can customize terms used to filter out data

The project I am involved with has three user roles who can all view documents. These documents are displayed or not depending on terms found in them: there are at least 10 categories of terms, but there might be more added over time. Assume there are a LOT of documents and a LOT of terms.

The first user role only sees the documents which do not contain any term from any of the 10 categories.

The second user role can view documents which do not contain any term from any of the categories that this user role selects.

The third user role can view documents which do not contain any term from any of the categories that this user role selects AND can customize the terms contained in a category.

How would you design a database for this without it being dead slow? String parsing nightmare.

It seems obvious that you need to index the documents as they are inserted, flagging the known terms. This way, you could perhaps have a boolean column for each category for example. This would easily take care of the first two user roles from a performance point of view.

But what about the third user role? Do you create a user table and periodically populate it with whatever terms are matched? This seems like a bad idea, as there will be a lot of users and the database performance will be hit. Or perhaps if you distributed the job over different machines?

And how would you store the third user roles customized lists, which will change over time?

Any ideas greatly appreciated.

Best regards, Bert Received on Sun Aug 06 2006 - 02:40:18 CEST