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:
Grades & blocks
We’ll present each subject area in the same order it is listed.
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:
last_name– The user’s first and the last name.
gender_id– References the
genderdictionary 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.
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
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
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
userwho 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
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
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
user_account_id– References the participant’s
time_joined– The timestamp when this participant joined this conversation.
time_left– The timestamp, if any, when this participant left the conversation.
user_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
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_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?