Scoring database entries by multiple columns

i'm faced with a situation, where i have to find the best matches for a users search request. I will provide an example (a little abstract):

We have a table with lawyers:

Name       Location   Royality    Family Law   Criminal Law
-------------------------------------------------------------
Lawyer A   Berlin     100 €/hour  false        true
Lawyer B   Amsterdam  150 €/hour  true         true

A user should now be able to search by several features. The weight of each feature should be some kind of parameter. In my case the table contains much more of such features (Location, Royality, 20+ boolean values). Of course the r开发者_C百科esult should provide all "good" results but ordered by some kind of "score", so the best result appears at the top.

I'm not looking for a out of the box solution rather than some introduction to this topic.

Kind regards,

matt

A generic approach is to assign a weight to each item, and add them up when they match. This will cause a full table scan to score every single record.

Assuming inputs of Berlin, >100/hr, Criminal Law=true, family law = null (no criteria) And Location match carries a weight of 5

select *
from (
    select *,
      case when location = 'berlin' then 5 else 0 end +
      case when royality >= 100 then 1 else 0 end +
      case when familylaw = null then 1 else 0 end +
      case when criminallaw = true then 1 else 0 end as score
    from tbl
) scored
order by score desc

You may be able to make use of SOUNDEX functions in your particular RDBMS. These compare two strings and give a numeric value for "how alike they sound".

You can then weight and/or sum the results for each column, as mentioned by Richard.