How to build/mantain an affinity score between 2 users?

Question

I mean analyzing 2 users profiles I get a score that it is reciprocal
a.affinity(b) == b.affinity(a)

I'd like to know in particular:

• which schema would u use to implement the affinity table

• which db mysql, redis,..

• which technology would you use to update the affinity score in background?

Thanks

Answer 1

For the db design part, I would recommend using the join/junction table concept, except in this case you're joining a table to itself. For example, if your user table looks something like:

UserId   UserName
------   --------
1        User Uno
2        User Dos
...

Then your join table would look something like:

UserIdA  UserIdB    AffinityScore
------   --------   -------------
1        2          56
34       208        137

where UserIdA and UserIdB are foreign keys to UserId in the first table. (You'd have to check and make sure that once UidA and UidB are set, e.g. values 1 and 2 respectively, that the relationship is not repeated if the values are swapped, e.g. values 2 and 1 respectively for UidA and UidB which could cause some weirdness in the app. You could use the CHECK constraint if using Sql Server or MySql.)

As far the join table concept goes, your choice of db does not matter. It's just a table with foreign keys. And for doing background updates, it depends on your platform. Could be a cron job initiating an executable or SSIS (Sql Server) running T-Sql script and many things in between.