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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
oo7mlAuthor Commented:
Thanks guys...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.