Matching, ranking based on relevance of each criteria

Question

I have a growing database containing:

• a table of demands (multiple criteria)
• a table of offers (multiple criteria)

Criteria can be string (e.g.: country name), boolean, numeric, ...

I would like to find all demand-offer which match more or less (a little like job banks, matchmaking, ...)

If tables didn't contain many rows I would calculate as follows:

• for each demand, calculate for each offer the relevance of matching by averaging the relevance of each criteria.

But for an important database this would take too much time, wouldn't it?

What solution do you recommend?

Answer 1

I'd do it the way you describe - but with a rolling caching mechanism and some indexes.

Work out the relevance on creation and then see how it goes. Incremental additions could be fine if the frequency is low (high-read, low-write). If it is high on both ends, you could split into two databases... process updates on one, then periodically push the post-processed data into the other, which is the default source for reading.