A Dating App Data Model

Emil Drkušić, Database designer and developer, financial analyst

by
Emil Drkušić
Database designer and developer, financial analyst

Posted: February 14, 2018

data model, model design, database design, dating website, dating app, love data model, valentine's

It’s Valentine’s Day and you’re single. Again. Not cool. Not cool at all. We’ll do something about that with today’s dating app data model. Ok, reading this article won’t automatically get you a date. Or maybe it will. Let’s see.

How many dating apps can you name? I can think of at least a dozen. As data modelers, though, we’re more interested in the data model underneath the app than the app itself. So let’s examine what it takes to run a data app, starting with what we need it to do.

What Do We Want to Achieve?

I guess that finding the “right” partner is why we download a dating app. Every definition of “right” varies, from the romantic if unrealistic Hollywood version to our own individual more down-to-earth scenarios. To me, the “right” would be someone who can tolerate your silliness (and you can handle theirs). But we all need to start somewhere.

These days, everything has gone digital – including dating, at least for the first few interactions. It’s easier to type a message than to go say hi to a person.

For any dating app, we need a way for users to register on our app, view and contact other users according to their preferences, and start a conversation with the lucky man or woman (or men and women). And this is where we’ll stop. The rest is up to the users.

We can assume that users will access our services either by a mobile app or our website. That’s not very important to our article; what is important is that we’ll need to store their data – using the database model we’ll discuss today.

Ready to dig in? Let’s get started.

The Data Model




The data model consists of three subject areas:

  • User accounts
  • Conversations
  • Grades & blocks

We’ll present each subject area in the same order it is listed.

Section 1: User Accounts

Section 1: User Accounts

The most important part of this model is the User accounts subject area. It’s where we’ll store all user accounts and their related data.

The central table in this subject area is the user_account table. Each user will create one account using their email address; that email, as well as other important details, are stored in this table. For each user, we’ll have:

  • first_name and last_name – The user’s first and the last name.
  • gender_id – References the gender dictionary and denotes the user’s gender.
  • details – The user’s text description of themself (presenting themself in the best way possible, of course :) )
  • nickname – A screen name that the user chooses.
  • email – The email address used during the registration process.
  • confirmation_code – The code sent to that email during the confirmation process.
  • confirmation_time – When user confirmed their email address.
  • popularity – The user’s popularity, as calculated from their grades and blocks. We’ll talk about grades and blocks later.

We have already mentioned gender. This is a simple dictionary we’ll use to store the different genders. Besides the primary key, the only attribute in this table is the UNIQUE name attribute.

Next, each user will choose the gender they are interested in. To facilitate that, we’ll need the interested_in_gender table. We’ll store UNIQUE pairs of user_account_idgender_id here.

The same logic is used again to manage user preferences and expectations. A list of all possible user preferences is stored in the relationship_type dictionary. As is usual with dictionaries, this one contains only one UNIQUE attribute, name. Values for this attribute can range from “romantic dinner” or “casual date” to something rather more explicit. I’ll leave it to you to fill the list of all possible values here.

And again, the user will select the list of relationship type(s) they are interested in. For each type they choose, we’ll store one record in the interested_in_relation table. The user_account_id - relationship_type_id pair holds only UNIQUE values.

The last table in this subject area is the user_photo table. When a user creates their profile, they’ll be able to post one or more photos of themself. For each photo uploaded, we’ll store:

  • user_account_id – References the user who uploaded this photo.
  • link – Links to the location where this photo is stored.
  • details – The user’s text description, if any, of the photo.
  • time_added – A timestamp of when this photo was uploaded.
  • active – A flag denoting if this photo is still active (i.e. available for others to see on the user’s profile).

Section 2: Conversations

Section 2: Conversations

Once we’ve defined our app’s users, we need to allow them to connect with each other. Exchanging messages is the most common way. The three tables we need for this functionality are grouped in the Conversations subject area.

I’ll assume that one user will start a thread, which we will call a conversation. For each thread, we’ll store the ID of the user that initiated that conversation and the timestamp of when it started. When a thread is closed, we’ll update the value of the time_closed attribute.

The user who started the conversation will invite one or more users to it. A list of all conversation participants is stored in the participant table. For each participant, we’ll have:

  • conversation_id – References the relevant conversation.
  • user_account_id – References the participant’s user account ID.
  • time_joined – The timestamp when this participant joined this conversation.
  • time_left – The timestamp, if any, when this participant left the conversation.

The conversation_iduser_account_id pair can contain only UNIQUE values and is therefore the alternate key of this table.

The last table in this subject area is the message table. For each message, we’ll keep the ID of the participant (participant_id) that sent that message, the message_text, and the timestamp (ts) when the message was sent. The order of the messages in a conversation could be determined using ts or id as the sorting column.

Section 3: Grades & Blocks

Section 3: Grades & Blocks

Last but not least is the Grades & blocks subject area. Everybody wants to date, but nobody wants to date the wrong person. To make online dating as comfortable as it can be, it’s nice to have some kind of rating system. I’ll implement two options.

The first is having users grade each other. Maybe you liked their profile or had fun messaging them. Or maybe you met in real life and felt completely okay. Why not give a grade to that person? To store this information, we’ll use the grade table. It will record the ID of the user who gave the grade, the ID of the user who was graded, and the actual grade given. The user_account_id_given - user_account_id_received pair will contain only UNIQUE combinations.

The final table in our model will allow users to block other users they don’t want to deal with. In the block_user table, we’ll store UNIQUE pairs of the ID of the blocking user and the ID of the blocked user.

We could calculate the popularity of each user based on the grades they are given and how many blocks they have.

So Do You Get a Date?

I guess reading this article didn’t automatically get you a date. I strongly encourage you to go out and meet someone in person. Still, if you find that using technology to make the first contact is more comfortable, that’s okay too.

No matter how you go about finding the “right” person, tell us your thoughts on this model. If you have used some dating apps, which features did you like? How do you think these could be added to our dating app data model?

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Find out how to identify all the factors of a successful visualization: think like your audience, find the right chart type, and take out the meaning of the data. View course Discover our other courses: