Database 101: How to Model Leaderboards for 1M Player's Game.
Tags: scylladb, database, yarg, tutorial
Published: 2024-01-29 17:34:16

Ever wondered how a game like **League of Legends**, **Fortnite**, or even **Rockband** models its leaderboards? In this article, we'll find out how to properly model a schema to handle them in a monstrously performative way!

If you’re just getting started with databases in general or databases, you might want to start off by reading my initial post, [Database 101: Data Consistency for Beginners](https://dev.to/danielhe4rt/database-101-why-so-interesting-1344) for Beginners. That article captures my own exploration of how many database paradigms exist as I look far beyond my previous experience with just SQL and MySQL. I’m keeping track of my studies in this **Database 101** series.

> It has been almost a year since I published my first article in this series! Thank you for being here with me as I learn this subject. Your comments and thoughts are always very helpful!

## 1. Prologue


![YARG Game Playthrough ScreenShot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dvensca2v67ma66vssnh.png)

Ever since I was a kid, like most average developers, I've been fascinated by games and how they're made. Speaking of which, let me introduce you to my favorite childhood game:  Guitar Hero 3: Legends of Rock. 

Well, more than a decade later, I decided to try to contribute with some games in the open source environment, like [rust-ro (Rust Ragnarok Emulator)](https://github.com/nmeylan/rust-ro) and also the main character of this article: the [YARG (Yet Another Rhythm Game)](https://github.com/YARC-Official/YARG).

YARG is literally another rhythm game, but the difference about this project is that it is completely **open source** and they united legendary contributors in game development and also design to make this project works. 

Suddenly the game was being picked up and played mostly by Guitar Hero/Rockband streamers on Twitch, and I thought: well, it's an open source project, so maybe I can use my database skills for something like creating a **monstrously fast leaderboard** or store the past games. 

It started as a simple chat on their Discord, which turned into a long discussion about how to make this project grow faster. 

Then I decided to talk to my boss and ask him if I could work with the YARG guys and the condition was to create something cool enough to implement [ScyllaDB (NoSQL Wide-column Database)](https://scylladb.com/) since I'm working as a Developer Advocate there. You won't believe how the simplicity and scalability brought by ScyllaDB perfectly fit the needs of YARG.in!

Anyway, talk is cheap. Let me show you some code and concepts!

## 2.  QDD - Query Driven Data Modeling

![NoSQL vs Relational](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ivkj9j8ni2fakkctx53n.png)

When we're talking about develop with **NoSQL**, mostly we should understand that depending on the paradigm (document, graph, wide-column etc) you should first understand **which query you want to run**. 

While in MySQL the main goal is to understand the consistency, in Scylla you should focus on the query and create your schema based on that query.

At this project, we will handle two types of paradigm, which are:
* Key-Value
* Wide Column (Clusterization)

Now let's talk about the queries/features of our modeling.

### 2.1 Feature: Storing the matches


![Submission Details YARG](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jaw4q7349upgrsa5p5g3.png)

Every time that you finish a YARG gameplay, the most interest thing would be submitting your scores together with many other in-game metrics. 

Basically it will be a single query based in a main index and that's all. 

```sql
SELECT score, stars, missed_notes, instrument, ...  
FROM leaderboard.submisisons  
WHERE submission_id = 'some-uuid-here-omg'
```

### 2.2 Feature: Leaderboard

![Leaderboard Figma File](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/69jp0vgxef71titt9ks0.png)


And now our main goal: a super cool **leaderboard** that you don't need to care about after a good data modeling. The leaderboard is per song, so every time you play a specific song, your best score will be saved and ranked. 

However there's a big point on this interface, which is having filters to know exactly "which" leaderboard to bring:

* song_id: required
* instrument: required
* modifiers: required
* difficulty: required
* player_id: optional
* score: optional

So imagine our query looks like this, and it returns the results sorted by score in descending order:

```sql
SELECT 
	player_id, score, ...
FROM 
	leaderboard.song_leaderboard 
WHERE 
	instrument = 'guitar' AND
	difficulty = 'expert' AND
	modifiers = {'none'} AND
	track_id = 'dani-california' 
LIMIT 
	100;

--   player_id  | score
----------------+-------
--        tzach | 12000
--  danielhe4rt | 10000
--     kadoodle |  9999
----------------+-------
```


Now we know the features that will be worked here, but can you already imagine how will be the final schema? 

No? Ok, lemme help you on that!

## 3.  Data Modeling time!

Time to take a deep dive into data modeling with ScyllaDB and better understand how to scale it.

### 3.1 - Matches Modeling

![End Game Screen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b6kedk7iu67zg7myj9mp.png)

First, let us understand a little more about the game itself:

- It's a rhythm game;
- You play a certain song at a time;
- You can activate "modifiers" to make your life easier or harder before the game;
- You have to choose an instrument (e.g. guitar, drums, bass and microphone).
- Every aspect of the gameplay is tracked, such as:
	- Score;
	- Missed notes;
	- Overdrive count;
	- Play speed (1.5x ~ 1.0x);
	- Date/time of gameplay;
	- And other cool stuff.

Thinking on that, we can easily start our data modeling, which will turn in something like this:

```sql
CREATE TABLE IF NOT EXISTS leaderboard.submissions (
	submission_id uuid,
	track_id text,
	player_id text,
	modifiers frozen<set<text>>,
	score int,
	difficulty text,
	instrument text,
	stars int,
	accuracy_percentage float,
	missed_count int,
	ghost_notes_count int,
	max_combo_count int,
	overdrive_count int,
	speed int,
	played_at timestamp,
	PRIMARY KEY (submission_id, played_at)
);
```

Let's skip all the `int/text` values and jump to the `set<text>`. 

The **set** type allows you to store a list of items of a particular type. I decided to use this list to store the modifiers because it's a perfect fit. Look at how the queries are executed:

```sql
INSERT INTO leaderboard.submissions (
	submission_id, 
	track_id,
	modifiers, 
	played_at
) VALUES (
	some-cool-uuid-here,
	'starlight-muse'
	{'all-taps', 'hell-mode', 'no-hopos'},
	'2024-01-01 00:00:00'
);
```

With this type, you can easily store a list of items to retrieve later. 

Another cool piece of information is that this query is a key-value like! What does that mean? 

Since you will always query it by the `submission_id` only, it can be categorized as a key-value.

### 3.2 Leaderboard Modeling


![Leaderboard filters Figma](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lpmzngra3jk5ipf3os3i.png)


This is the part of the article where you will learn some cool wide-column database concepts.

On our leaderboard query, as mentioned earlier, we will always need some dynamic values in the WHERE clauses, which means that these values will belong to the **Partition Key** while the **Clustering Keys** will have value that can be "optional". 

A **partition key** is a hash based on a **combination of fields** that you added to identify a value. Did you get it? No? Well, it took me a while to understand that too, but let me show you something:

Let's imagine that you played `Starlight - Muse` 100x times. If you would query this information, would be 100x different results differentiated by Clustering Keys like `score` or `player_id`.


```sql
SELECT 
	player_id, score ---
FROM 
	leaderboard.song_leaderboard 
WHERE 
	track_id = 'starlight-muse' 
LIMIT 
	100;
```

If 1.000.000 players play this song, your query will become slow and it will become a problem in the future, because your partition key consists of only one field, which is `track_id`.

However, if you add more fields to your **Partition Key**, like mandatory things before playing the game, maybe we can shrink these possibilities for a faster query. Now you see the big picture? Adding the fields like: **Instrument**, **Difficulty** and **Modifiers** will give you a way to split the information about that specific track evenly.

Let's imagine with some simple numbers:

```sql

-- Query Partition ID: '1'
SELECT 
	player_id, score, ...
FROM 
	leaderboard.song_leaderboard 
WHERE 
	instrument = 'guitar' AND
	difficulty = 'expert' AND 
	modifiers = {'none'} AND -- Modifiers Changed
	track_id = 'starlight-muse' 
LIMIT 
	100;

-- Query Partition ID: '2'
SELECT 
	player_id, score, ...
FROM 
	leaderboard.song_leaderboard 
WHERE 
	instrument = 'guitar' AND
	difficulty = 'expert' AND
	modifiers = {'all-hopos'} AND -- Modifiers Changed
	track_id = 'starlight-muse' 
LIMIT 
	100;
```

So, if you build the query in a specific shape it will always look for a specific token and retrieve the data based on these specifics Partition Keys.

Let's take a look at the final modeling and talk about the clustering keys and the application layer:

```sql
CREATE TABLE IF NOT EXISTS leaderboard.song_leaderboard (
	submission_id uuid,
	track_id text,
	player_id text,
	modifiers frozen<set<text>>,
	score int,
	difficulty text,
	instrument text,
	stars int,
	accuracy_percentage float,
	missed_count int,
	ghost_notes_count int,
	max_combo_count int,
	overdrive_count int,
	speed int,
	played_at timestamp,
	PRIMARY KEY ((track_id, modifiers, difficulty, instrument), score, player_id)
) WITH CLUSTERING ORDER BY (score DESC, player_id ASC);
```

The partition key was defined as mentioned above, consisting of our **REQUIRED PARAMETERS** such as: track_id, modifiers, difficulty and instrument. And on the **Clustering Keys** we added **score** and **player_id**.

> Note that by default the clustering fields are ordered by `score DESC` and just in case a player has the same score, the criteria to choose the winner will be `alphabetical` ¯\\_(ツ)_/¯.

First is good to understand that we will have only **ONE SCORE PER PLAYER**, but with this modeling like that if the player goes through the same track twice in different scores, it will generate two different entries. 

```sql
INSERT INTO leaderboard.song_leaderboard  (
	track_id, 
	player_id,
	modifiers,
	score,
	difficulty,
	instrument,
	stars, 
	played_at
) VALUES (
	'starlight-muse',
	'daniel-reis', 
	{'none'}, 
	133700, 
	'expert', 
	'guitar', 
	'2023-11-23 00:00:00'
);

INSERT INTO leaderboard.song_leaderboard (
	track_id,
	player_id,
	modifiers,
	score,
	difficulty,
	instrument,
	stars, 
	played_at
) VALUES (
	'starlight-muse',
	'daniel-reis', 
	{'none'}, 
	123700, 
	'expert', 
	'guitar', 
	'2023-11-23 00:00:00'
);


SELECT 
	player_id, score
FROM 
	leaderboard.song_leaderboard 
WHERE 
	instrument = 'guitar' AND
	difficulty = 'expert' AND
	modifiers = {'none'} AND
	track_id = 'starlight-muse' 
LIMIT 
	2;

--   player_id  | score
----------------+-------
--  daniel-reis | 133700
--  daniel-reis | 123700
----------------+-------
```

So how do we fix this problem? Well, it's not a problem per se. It's a feature! LOL

As a developer, you have to create your own business rules based on the project needs, and this is no different. What do I mean by that?

You can run a simple **DELETE** query before insert the new entry and guarantee that you will not have a specific data from the **player_id** with less than the new **score** inside that specific group of **partition keys**. 

```sql
-- Before Insert the new Gampleplay

DELETE FROM 
	leaderboard.song_leaderboard 
WHERE 
	instrument = 'guitar' AND
	difficulty = 'expert' AND
	modifiers = {'none'} AND
	track_id = 'starlight-muse' AND
	player_id = 'daniel-reis' AND
	score <= 'your-new-score-here';

-- Now you can insert the new payload...
```

And with that we finished our simple leaderboard system, the same one that runs in YARG and can also be used in games with MILLIONS of entries per second :D

## 4. How to Contribute to YARG

Here's the part of the text that I'll invite you to contribute on this wonderful open source project!

Today we're building a brand new platform for all the players using:

- Game: Unity3d [(Repository)](https://github.com/YARC-Official/YARG)
- Front-end: NextJS [(Repository)](https://github.com/YARC-Official/yarg.in)
- Back-end: Laravel 10.x [(Repository)](https://github.com/YARC-Official/yarg-api)

and we will need many developers and testers as possible to discuss future implementations of the game together with the main contributors!

![YARG Discord](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y5b2pdxvrth2o6jfmada.png)

First, make sure to join their [Discord Community](https://discord.gg/sqpu4R552r). There is the place where all the technical discussions happen with the back of the community before going to the development board.

Also, outside of Discord, the YARG community is mostly focused on the [EliteAsian](https://twitter.com/EliteAsian123) (core contributor and project owner) Twitter account for development showcases. Be sure to follow him there as well.

{% embed https://twitter.com/EliteAsian123/status/1736149319382671766 %}

And FYI, the **Lead Artist** of the game, aka [Kadu](https://twitter.com/kaduyarg) is also a **Broadcast Specialist** and **Product Innovation** Developer at **Elgato** that worked with streamers like: 
* Ninja 
* Nadeshot 
* StoneMountain64
* and the legendary DJ Marshmello.

Kadu also use his twitter to share some insights and early previews of new features and experimentations for YARG. So, don't forget to follow him on Twitter as well!

{% embed https://twitter.com/kaduyarg/status/1689489132060397568 %}

Here is some useful links to know more about the project:
* [Official Website](https://yarg.in/)
* [Github Repository](https://github.com/YARC-Official/YARG)
* [Task Board](https://yarg.youtrack.cloud/agiles/147-7/current)

> Fun fact: YARG got noticed by [Brian Bright](https://twitter.com/BrianBright/status/1744533504531317194), project lead on Guitar Hero, who liked the fact that the project was open source. Awesome, right?

## 5. Conclusion

Data modeling is sometimes challenging, and this study took 3 months of many new ScyllaDB concepts and a lot of testing together with my community at Twitch. 

I have also published a [Gaming Leaderboard Demo](https://github.com/scylladb/gaming-leaderboard-demo), where you can get some insights on how to implement the same project using **NextJS** and **ScyllaDB**!

Also, if you liked ScyllaDB and want to learn more about, I strongly suggest you to watch our free [Masterclass Courses](https://lp.scylladb.com/masterclass-ondemand-main?siteplacement=navigation) or vising [ScyllaDB University](https://university.scylladb.com/)!

Don't forget to like this post, follow me on the socials and fill your water bottle xD 

See you in the next article!

[Follow me on Twitter](https://twitter.com/danielhe4rt)
[Follow me on Github](https://twitter.com/danielhe4rt)
[Follow me on Github](https://twitter.com/danielhe4rt)
[Follow and Subscribe at my Twitch Channel](https://twitch.tv/danielhe4rt)