Something that struck me throughout my self-taught journey is that there seems to be a weird lack of data-modeling guides for newer devs (particularly front-end) outside of education meant for database-admins and the like. There is now a good overview of this sort of thing in Andrei Neagoie's SQL & Database course, but today I'm going to go over a real-world example of how to model data! This will be for a Hasura back-end, so I will be using some terms like 'text' instead of string to describe certain data-types. Some familiarity with the high-level basics of relational databases will be helpful for grokking this article, but I'll be linking certain terms to definitions regardless.

Step One: Determine the Entities

An entity in database terminology is a person, place, or thing. It will have some sort of identifier, a singular name, and will contain more than one instance of data. These attributes help us decide whether or not business logic should be a full-fledged entity. The application in question is a character tracker for the Marvel Puzzle Quest game (repo here), and I need two entities:

• user, to represent the users of the application
• character, to represent the in-game characters that belong to individual users.

In this phase, it's good to plan ahead and think about how entities are related. In this dead-simple example, individual users will have many characters, but individual characters will belong to only one user. Now, what sort of instance data needs to belong to these entities?

Step Two: Determining Entity Data

In this step, we determine what pieces of data belong to each entity. When deciding what belongs here, we want to make sure that the data plays a necessary role in the application and that it's something that we have a good reason to persist in the database, as opposed to a value that we can easily derive from other values.
For the user I'm going with:

• id, which will be text/a string. This will be unique, as it serves as the primary key for the users table. This will also let us uniquely identify the characters that belong to a certain user, but I'll talk more about that when I get to the characters table.

• name, which will also be text, so that we can quickly identify users when doing administrative functions in the dashboard.

• created_at, which will be a timestamp to identify when the user's account was created.

• last_seen, which will be another timestamp that tells us when the user was last logged in.

These particular fields are pulled directly from Hasura's back-end tutorial, and I had not used the timestamp values in some past projects, but I'm using them here because the hosting for my application is limited, and I'll want to know if certain users sign up once and aren't using the service. Now for the characters:

• id, which will again be text, unique, and the primary key for this table. It's possible that a particular table could do without a unique id, but excluding this makes it a lot harder to query.

• user_id, text, this is the id of the user a character belongs to, making this value the foreign key that links characters to users.

• name, the character name, which is text.

• char_level, an integer, which represents how advanced and powerful that character is in-game.

• image, which will be text, represents the filename for the image associated with each character.

• rarity, which is a measure of how rare a particular character is. There are different systems in play depending on this, so it's an important attribute.

• power_one_level, power_two_level, and power_three_level are all integers that represent how highly-leveled a particular power for a character is. I'll note here that characters can have between 0-5 levels in a power, and no more than 13 power levels in total. I mention this to point out that total power level, which is a mathematic function of the combined power levels, and not something we need to store in the database. The power levels are one of the few character attributes that the user will be able to modify directly.

• power_one_color, power_two_color, and power_three_color are the colors associated with each power, to be represented as text. This will be important for the UI of the application.

• shards, which are an in-game reward currency represented here as an integer. The whole point of the application is to give users a faster and more browsable snapshot of their in-game lineup so that they can make informed decisions about where to allocate rewards, and how many shards a character has at one given time is an important factor in that decision.

• feedees are the characters that receive in-game rewards from leveling certain characters, represented here by text. A character can have 1, 2, or no feedees currently. I'll note that this data type wouldn't be my first choice for this particular value, and I'll go into more detail about that in the next section.

Step Three: Consider Quirks and Limitations Across Your Stack

Once you have the basic data modeled, you'll want to stop and consider if there are any idiosyncrasies or special cases throughout the stack that you need to make adjustments for. An example of this is the aforementioned feedees field above: as a JS dev, I tend to think in the types in that language, and I thought of this as an array of strings. However, I had forgotten that Hasura doesn't support basic arrays (outside of arrays that are created in the form of one-to-many relationship). So I was faced with two choices: a) create an entirely new table for feedees that has a one-to-many relationship with characters, or b) do something else. Option A seemed like a headache for a simple field like this, so I opted to make feedees text, and I use a function on the front end that parses the string and creates an array when there's more than one feedee.

This can be vital if you haven't chosen a back-end stack: in my particular use case, I don't have a lot of resources I'll need to run this parsing function on, so it's fine. But if you have to scale to an unknown number of resources? It might be better for you to use a NoSQL DB like MongoDB for a use-case like a scenario I just described. If you have very specific needs for the shape of your data, your stack will need to reflect that.

Step Four: Plan Out Data Flow Throughout the Application to Catch Mistakes Before You Code

In an earlier iteration of this model, there was a feeder field, to represent the character that feeds another character. When I was writing up the front-end, I realized this field played no part in the application and was completely unnecessary. Every application, regardless of complexity, can be reduced down to "takes some data and passes it from one place to another, and maybe transforms it somewhere along the way". Once you have an outline of the data model, it's good to sit down and plot out how each piece is utilized from beginning to end, to find inconsistencies and errors before you actually create the database itself.