Database Design Options (Performance Choice)

Hi,

I am building a small dating app (very similar to Tinder).

The app allows users to checkin to venues, and then they can anonymously 'like' other users who have also checked in to the same venue. If two users happen to like each other, it's a Match.

I have the following tables:

USER
ID
Name
Gender
Email
Password

VENUES
ID
Name
GeoLocation

CHECKIN
ID
UserID
Venue
TimeStanp

LIKES
ID
TimeStamp
Sender
Receiver
Venue

One of the main tabs on the app is called Matches, which shows all of your matches.

QUESTION

I am trying to decide whether I should create a MATCHES table and insert a record into it every time there is a match between two users, OR should I just query the LIKES table and return the matches from there?

The LIKES table will no doubt be the largest table in the database, so I thought creating a MATCHES table would greatly improve performance. Once a record is put into the Likes table, it will not be updated or deleted, so there will be no mismatch of information, however i still think i am duplicating data here...

What is your opinion on this? What option would you go with? Thanks in advance.
oo7mlAsked:
Who is Participating?
 
pcelbaCommented:
As I understand nothing is done yet but you are thinking about the performance. Is that right? How many users do you have already?

If you are talking about 100000 records then there is no need for any data structures optimization except appropriate indexes creation.

Create the app as fast and simple as possible, publish it, and while the number of users will grow you may update the data model behind to optimize the speed...
0
 
dsackerContract ERP Admin/ConsultantCommented:
At first glance, my thought was to simply create a VIEW that would "match" the LIKES table. However, upon second thought, you (or potential subscribers) may be able to use a MATCHES table, perhaps with some extra fields that track actual results (WeDatedFlag, HateThisPersonFlag, etc *lol*).
0
 
oo7mlAuthor Commented:
Thanks i was thinking the same, and considering the original Likes won't be changing / updated, there is no harm in moving away from them and onto some other piece of data to handle the Match feature.

What would you put in the Match table:

ID
UserFirstLike
UserSecondLike
VenueMatched
WeDated
0
 
Walter RitzelSenior Software EngineerCommented:
To add to what @dsacker mentions, think about how many likes are you going to store. Think about in the policies that you may want to implement to archive likes.This table can be huge, you may want to archive likes every quarter, so maybe a match calculated on that dinamically will not be available right away...
Also, the number of matches will be far less than the number of likes, so if you store that in its own table, you may have better performance.
0
 
oo7mlAuthor Commented:
Thanks guys...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.