I am finding users that have liked each other. I have a user table and matched table:
create table matched
(match_id int identity not null primary key
insert into matched values('A','C',null,'N'),
select * from matched;
When a record is inserted (when a user is interested in another user), I check to see if that user is also interested in them. If so, I update the interested_by_user_id and the mutual_yn to Y.
In my example, once the 4th record is inserted, it detects an interest exists for A and C, so for the 4th record it would update interested_by_id with 'A' and mutual to 'Y' and in the first record it would update interested_by_id = 'C' and mutual to 'Y'. Then I query to determine everyone's likes.
But is this a good or best way? Performance-wise it should be OK, even with millions of records. With a thin and indexed table it should be fast.
- This topic was modified 1 month, 3 weeks ago by dbgaragedays.