Database architecture with extension in mind

Question

Exposition

Let's say our system has users and some games. Let's call these games A, B and C.

For simplicity's sake, our initial tables look like this:

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS games (
    id SERIAL PRIMARY KEY
);

Problem

Whenever we play these games and finish them, we have to record statistics related to the game for the specific user. For example, for game A we have to record total_jumps and total_crouches because the game's mechanics allow such accumulation, however games B and C might require us to record other total_* fields (though, the prefix total_ is not necessary). How do I store this information?

I'm troubled with the solution for this problem. I have come up with 2 approaches:

Solution 1

Have a single table for this:

CREATE TABLE IF NOT EXISTS statistics (
    user_id SERIAL REFERENCES users (id) ON DELETE CASCADE,
    game_id SERIAL REFERENCES games (id) ON DELETE CASCADE,
    fields JSONB,
    PRIMARY KEY (user_id, game_id)
);

This way we leave it to the application level to parse arbitrary data coming from the fields column.

Solution 2

Have multiple tables, one for each game:

CREATE TABLE IF NOT EXISTS A_statistics (
    user_id SERIAL PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
    total_jumps INTEGER,
    total_crouches INTEGER
);

---

Personally, solution #2 looks better to me, but... this problem still bothers me a lot. As if I am missing something, because neither of the options make extension (adding new games) any easier? Say we add games D, E and F. Both solutions require us in some way to handle specific fields (at the application level) required for these games. Is that something I just have to come to terms with? Or is there a third, better solution to this that I just don't see?

Answer 1

You could use a non-relational database to store this kind of data. For example, in mongoDB you could have a games collection. Then in your application code, you might have multiple data models:

FootballGame total_passes

BoxingGame total_punches

Each of these data models would save to the same collection, which is made possible due to the use of a non-relational database.

Further, each of these data models could inherit from a base Game model with some common properties.

Each data model will store some kind of discriminator which denotes the game. This would be needed so that your application code knows how to load a saved game from the database, i.e. what data model the document saved corresponds to. See https://mongoosejs.com/docs/discriminators.html#discriminators

Answer 2

Relational database management systems expect well-defined data structures - the "relations" in the name. So having runtime-variable needs is a known hard problem.

That said, most recent versions of everything handle JSON well. Having some JSON parsing in a SQL query need not hurt performance much.

Starting with a single JSON column will get you up & running. Over time you will find that many queries read the same metric, or certain games are overwhelmingly popular. Add a column for these specifically and write the value to the column and also to JSON. In other words, start with 1 and move to 2 over time.

If you research long enough you will read about the "entity attribute value" model. It will work well enough at small scale (depending on hardware) but, in my experience, gets real ugly real quick once you're no longer small.