| Unsocial media: The Data
A journal of building an awful social media based MMO.
Nov

Overview

Now that we have an idea of where we’re headed, we can look at the technical side of things. We’re going to start with the data, since the way it’s shaped will inform a number of decisions going forward.

Tables

I plan on having us use a relational database, and make use of the Postgres JSON type for fields which contain an arbitrary number of subfields. It will also make API responses easier to digest for our frontend and keep things simpler, meaning less queries overall, on the backend.

We’re only going to use three tables to get us started:

  • Region
  • User
  • Inventory

I’m sure that we’ll add other tables later on, but to get us to version 0.1 this is sufficient for mapping out the world, adding users and tracking player items.

Region

To start with, let’s look at how we might define a region, which I see being composed of the following:

  • Location (x, y)
  • Dimensions: width and height
  • Posts
  • Date created
  • Date updated

Location and dimension

Location will be broken into the fields x and y, which represent the top left corner of a region. Remember that we’ve determined the fixed size of our regions to be 3200x2048 in the previous post, and divided each of those into 64x64 blocks, with all images being square and all text fields being wider than their height.

Location id

(x, y) defines region location with size fixed at 3200x2048.

Part of me would like this to be more flexible, but I believe that this sort of flexibility will cause a cascade of difficulties later on. By keeping the base components of the world static in size we can reduce the headache of needing to accommodate unforeseen edge cases that can result from adjustable dimensions. Given the size of a user’s screen, and the size of a region, we can determine how many, and what regions, are currently in view. For example, if we take the top left of the screen as the current world location, and find we’re at (8000, 5000), we can find that our x of the coordinate by using modulus ((8000 mod 3200) * 3200) is 6400. We can find the y value in a similar way using 2048 instead. One thing you may have notice which is a byproduct of using statically-sized regions, is that we don’t even need to store the actual x value, but instead could say this region is at 2 since 2 * 3200 is 6400. In the image below that would be the leftmost overlapped region.

User’s screen

The user’s screen overlaps two regions.

We will say that regions will only become a database record if someone has performed an action in that space, which means that at any given point the user will view, at least in part, anywhere between 0 and 4 queryable regions. Unless they have a massive monitor, though that is an edge case we’ll look at later on.

Posts

This is a JSON field which contains any number of posts. Using a JSON type for this gives us a few benefits:

  1. It means we only need one query to get the whole region’s posts.
  2. It can be easily consumed by the front end.

Alternatively we could have created a Posts table, but then we’d be doing an excessive number of JOINs each time we load a region. If a region has 30 pots in it, then multiply that by up to four possible regions at a time per user, and the database will need to JOIN 60 records each time someone views this area of the world. Compare that to just four needed if we use the JSON field.

At this point, some of you might be wondering why I didn’t choose a NoSQL database, especially given that what we have so far would fit quite well. To some extent I agree, but I’ve personally had trouble later on when a database becomes more complex when using NoSQL. I know that I’ll want to add tables and these will be related to the existing data, and as such, decided to stick with what I know works well. Also, if at some point we need to scale out and decide to switch to a NoSQL database to make that easier, it’ll be less difficult to go from a relational database to NoSQL instead of vice versa, or at least that’s how I see it.

Keep in mind that the Post field is going to evolve as we go forward, but for now I see it functioning as such.

  • A person enters a new region and mines a tile (64x64) chunk.
  • The server will create a record in the database.
  • The server fills the space with randomly sized message/image spaces in the Post field.
  • Users can dig them out.
  • A message/image space can be used once fully dug out.

The key is that a region is not defined until a person first explores it. The Post field holds the skeleton outline of a region after first exploration, and each entry will contain the following:

  • Offset from the region’s coordinates.
  • Message or image data.
  • UserId
  • Date created
  • Date expires
  • Additional attributes
  • Badges

The bulk of the transmitted post will be between the data and the additional attributes. These additional attributes I’m going to leave open for the time being, but they will include things that change the appearance or abilities associated with the post. It could, for example, include additional CSS or HTML or a flag that will allow specific actions within the post.

1
2
3
4
5
6
7
8
{
   
   data: “Last week I ate a sandwich…”,
   attr: {
               text: “font-color: #452a09”
               action: “on:hover …”
   }
}

The badges are going to be those elements (as discussed in the previous post) which can be given to other people’s posts and may enable specific actions/effects. It may be a single badge or a combination which does certain things to either their post or others within the region. But we’ll get back to that in a later post.

User

We plan to keep onboarding as simple as possible by allowing anonymous users. This has negatives with regard to moderation and stopping bots, though I’m hoping the nature of the site and a few other measures will prevent most issues.

Users can also create an account, but I’d like to keep this simple and dumb. Remember, this site is not supposed to be good in the traditional sense. Usernames will be unique, but users will also be assigned a key which only they’ll know. In the User table then, we’ll only need a few things:

  • username
  • password
  • email (optional)
  • key
  • anonymous

With the exception of email and password, every field will be defined the minute a person starts gathering resources. At that point, though, they will be anonymous, named as anonymous- and then have the choice of setting a username, password and email. Once it has been set they’ll not be able to change it, and any anonymous posts they’d already made will continue to be under that anonymous name. Email will be optional, and only for password recovery if desired.

Inventory

The last table will be Inventory, and this is the most frequently updated table we have. It will include the following entries:

  • user id
  • Resources
  • Items
  • Badges
  • Abilities

Resources, which we’ve discussed as being things like dirt and stone (definitely subject to change) will be broken into multiple fields, while the remaining will need to be flexible, and will be JSON types composed of a varying number of subfields. For example, the Items field might contain an entry like:

1
2
3
4
{
  "goldenAxe": true,
  "psychedelicToad": 3
}

Both Badges and Abilities will be structured in the same way, allowing us to easily parse and handle information on the client side, while having enough flexibility in our data to add, remove things as we go forward.

Wrapping it up

This covers everything we’ll need in order to create our database, which we’ll do in the next post when we start building the site itself. We’ll lay out the base of our SvelteKit app and get the database going using Edgedb, something I’ve been wanting to try out.