Which Query Method Is Better?

Hi,

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

The application 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.

 

It depends, is there, by any chance, a button to "Unlike" , and if so, what happens with the rows from MATCHES table related to that user in this case ?
No doubt there's a performance increase, still you'll have to choose between a `delete on cascade`with `key constraints` or you could simply query the matchs DB like the following:

DELETE * FROM `Matches` WHERE userID = [USERID] AND venueID = [UNLIKED_VENUE_ID];

Code (markup):

Cheers ( and dont forget to index the userID , venueID fields ).

 

Hi, there is not an option to unlike. Once a like is recorded in the DB, there won't ever really be a reason to adjust or update it again.

Do you think i should create a Matches table or just query the Likes table?

 

Sounds like an interesting idea.....also sounds very difficult to launch. You check in to an exact location and others who have checked in to the same location can Like you? Hope you have a brilliant idea to mass market this...

 

Yeah... depending on the venues, it might be a very small market - even Tinder, in smaller areas, is more or less a bust - given that you have maybe a 1/100 chance in matching with someone (Tinder is 95% looks, maybe 5% mutual friends and interests), if you have 20 potential matches, that's not great odds However, technically speaking, it might be wise to have a matches-table - you could even create entries in this table using only triggers, and if you do, you could do a pull say every 30 seconds when at the actual location (ie, no need to do checks if you're not at a registered location) - etc. etc.

 

It would really hinge to me on how you could index the matches table -- generally speaking if disk space and RAM are not a concern, you want to optimize for whatever is done the most or takes the longest. Someone hitting "like" making a record in "matches" is a short operation that would take no major time no matter how often they do it. Writes are most always short operations. A cross-referencing query of the LIKES table on the other hand is a big query and if you can make a MATCHES database that does it faster, and you have the disk space to do it, the minor impact when someone hits "like" is probably well worth the effort.

It's one of those bits of optimization people often don't think of; it's like posts on a forum -- typically a post is made once but accessed many times as such inefficiencies in writing the post to the database are easily dismissed, while inefficiencies in reading or searching posts can be crippling. That's why many forums and CMS maintain dictionary/lookup libraries of words in addition to storing the post when the post is created or edited - so as to optimize the operations (like search) that are likely to be called more often and take longer.