How I Designed A Job Application Database With DynamoDB

Designing the data model for a job app database with the single table design.

Designing a job app database

I built a job board mobile app for an idea I had with a partner of mine last year.

The idea was innovative and would help solve an long-standing issue in the job search sector.

To make a long story short, the app idea didn’t work out at the research phase and we scrapped the project.

However, from that project I gained yet another learning experience. Specifically, DynamoDB data modeling in a use case I had never worked on before.

In this article, I’ll take you through the database I designed and how I planned it out for scalability and speed from the get-go.

Overview

The job board application MVP was designed to make job searching an easy and less grueling process for job seekers. It offered a swipe-action style app, like Tinder’s app to let users find jobs in a gamified manner.

Here are the main entities that I stored on the single DynamoDB table:

• seekers (users searching for jobs)

• employers (employers posting jobs)

• jobs (the actual job posting)

• suggestions

• matches

• messages

Apart from messages, all of these entities were related and I had to find a way to store and fetch related items together.

To do this efficiently, I started by identifying the main access patterns for the MVP.

Identifying The Access Patterns

Here were some of the main access patterns I had on the app:

1. Get job postings sorted by date

2. Get job postings within the last month

3. Get seeker’s job suggestions sorted by relevance

4. Get job matches by date

5. Get an employer’s job postings

6. Create a job

Designing The Data Model

As the main feature of the app was to get job postings and suggestions for job seekers, the database’s data model was heavily influenced by this.

The base table’s primary keys were designed to support job postings within a certain location. There were a few GSIs to support the other access patterns.

Here’s an extract of the table’s data.

Job App database — single table design

A few things to point out here.

Highlighted in yellow are the job items. These are identified by their city (partition key) and the sort key is structured as follows:

• a prefix letter “j” so we can visually identify job items

• a date prefix so results can be sorted by date (of job posting)

• a string representing the zip code (postal code)

• a string (uuid) for the actual item ID (I used simpler numbers like 101).

This sort key design enables a few access patterns out of the box.

We can get all jobs in a given city. We can get all jobs that were posted within a given date range. And further, we can get all jobs within a zip code range.

For suggestion items, we have a similar primary key structure except we add a relevancy score (R1, R2, or R3).

Depending on an algorithm’s calculations of how relevant a job is for a user, a suggestion will get stored on our database with a relevancy score of 1 to 3.

Each suggestion item is also given a TTL attribute of approximately one week so it can automatically expire (to optimize storage and costs).

Match items work similarly — a match is created when the employer approves the job seeker’s request.

Let’s see how we can satisfy these access patterns and others below.

The Queries Design

1. Get job postings sorted by date

Based on the primary keys we discussed previously, we can satisfy this access pattern like this:

TableName: "job-app",
KeyConditionExpression: "pk = 'job#montreal#' AND begins_with(sk, 'j#')"

This returns all jobs in the given city without performing any filtering.

If we needed to filter deeper by date and zip code we can add the date or zip code inside the begins_with() method, after the ‘j#’ string.

2. Get job postings within the last month

To get all job posted within the last month we can run a BETWEEN query. We can specify to start with all job items whose sk date strings begin with “2023–08–01” until the end of that month “2023–08–31”.

Regardless of whether there is a 31st of the month or not, it will give us the results we need and we don’t need to worry about whether there are 30 or 31 days that month.

TableName: "job-app",
KeyConditionExpression: "pk = 'job#montreal#' AND sk BETWEEN 'j#2023-08-01' AND 'j#2023-08-31'"

3. Get seeker’s job suggestions sorted by relevance

Provided with a seeker’s userID and a relevancy score we can fetch that seeker’s suggested jobs, using the GSI (GSI1PK and GSI1SK).

TableName: "job-app",
IndexName: "GSI1",
KeyConditionExpression: "GSI1PK = 'seeker#201' AND begins_with(GSI1SK, 'j#R1')"

This will get us all job suggestions for that user that have a relevancy score of 1 (highest).

4. Get job matches by date

TableName: "job-app",
KeyConditionExpression: "pk = 'seeker#201' AND begins_with(sk, 'j#2023')"

The above query will get me all job matches on the current year. If I want to filter further by month I can add “-08” after the year to specify the month.

If I want to filter further by date range I can use the BETWEEN clause like in the previous query.

5. Get an employer’s job postings

To get an employer’s job postings we can again use our GSI1.

TableName: "job-app",
IndexName: "GSI1",
KeyConditionExpression: "GSI1PK = 'employer#301' AND begins_with(GSI1SK, 'j#')"

That will return the first two items in our database highlighted in yellow.

6. Create a job

We know how to query for jobs by various access patterns and filter them down now let’s look at how to create a given job on our database.

We need to keep in mind to add all the relevant attributes especially the GSI1PK and GSI1SK

TableName: "job-app",
Item: marshall({
  pk: `job#${city}`,
  sk: `j#${currentDate}#${zipcode}#${jobID}`,
  entityType: "job",
  GSI1PK: `employer#${employerID}`,
  GSI1SK: `j#${currentDate}#${zipcode}#${jobID}`,
  ...rest
}),
ConditionExpression: "attribute_not_exists(pk) AND attribute_not_exists(sk)",

We model the partition and sort key exactly how we need them for our previous queries and any access patterns we may require.

We also check if the item doesn’t already exist in our table with the “attribute_not_exists()” condition expression.

That satisfies all of the app’s main access patterns.

Conclusion

Even though the app didn’t work out, designing the database was a great learning experience in making DynamoDB work for real-world use cases.

This job app database design was challenging, particularly on finding the best method to partition jobs in an efficient and scalable manner.

As with every DynamoDB table design, understanding the access patterns is the best strategy for modeling the data for maximum efficiency, performance and scalability.

I hope you learned something of value from my experience building it!

---

👋 My name is Uriel Bitton and I’m committed to helping you master Serverless, Cloud Computing, and AWS.

🚀 If you want to learn how to build serverless, scalable, and resilient applications, you can also follow me on Linkedin for valuable daily posts.

Thanks for reading and see you in the next one!