0

My Schema Diagram

I'm creating dating app like Tinder with Spring Boot and want to make sure that even with huge amount of data it will be working with best possible performance.

My tables looks like above. All tables and actions reference profiles.id.

Question 1:

Should I make separate table for men and separate for women or stay with one table and when querying filter by 'gender' using WHERE?

Question 2:

While fetching for 50 profiles to swipe, should I do it like that:

  1. Getting whole 'profiles' table with all columns filtering by 'gender'

  2. Deleting already swiped ones (backend server job)

  3. Shuffling (backend server job)

  4. Selecting first 50 out of the rest of profiles (backend job)

or like that:

  1. Getting all profile ids

  2. Deleting ids of already swiped profiles

  3. Shuffling (backend server job)

  4. Getting first 50 profiles by ids, one by one or using condition IN

?

Second way sounds better assuming huge database and many users requesting that simultaneously, but is it correct answer to that case?

Improve this question

1 Answer 1

Reset to default
0

Should I make separate table for men and separate for women or stay with one table and when querying filter by 'gender' using WHERE?

I can't see any reason in the world to use separate tables for men and women. Even if you want to limit matches to a man and a woman (and exclude same sex matches) you will use the profiles table in a lot of places outside of matches and using separate tables would be a real pain.

Performance wise, even if you had millions of users, it will make no difference.

Getting whole 'profiles' table with all columns filtering by 'gender'

Deleting already swiped ones (backend server job)

Shuffling (backend server job)

Selecting first 50 out of the rest of profiles (backend job)

All this can be processed in the database with a single SQL query, which can do it a lot more efficiently than you could in your code.

SELECT * FROM profiles WHERE gender = 'F' --filter by gender AND NOT EXISTS (SELECT 1 FROM swipes WHERE swipes.receiver_id = profiles.id AND swipes.sender_id= #id_of_current_user#) -- exclude already swiped ones ORDER BY random() --shuffle using a random number generator LIMIT 50 --take first 50
Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.