I'm in charge of designing the entire backend for the REST API of an application that works more or less like an online browser game (think OGAME, Travian, and the likes). In this game, players are able to loot procedurally generated equipment, like in Borderlands. This equipment can have any amount of bonii to all the different stats present in the game; so to speak, a gun could potentially grant you more Health or Intelligence.
Right now, they have given me a database schema they were using as a Proof of Concept. Said DB schema is in dire need of a redesign, and since we are in such early stages of development, I might as well go all in with it.
My problem is as follows: the server application generates random equipment on demand, and the generated equipment is stored in the database. Depending on the type of the generated piece of equipment, it may appear either in the Weapons table (1:n with the player, using a third table [?] for the relation), the Armor table (1:n with the player, same as with the Weapons table), the Ammo table (1:1 with the player, since Ammo types are hardcoded) and the Consumables table (1:1 with the player, same as with the Ammo table). Armor and Weapon tables are similar, but not equal, so merging them in one table isn't possible unless we use a solution similar to what I am about to ask.
One of the usecases is, obviously, listing the player's current Inventory. As is, this would take three different queries (I'm not expecting a huge concurrent user volume, but I'd rather be safe than sorry). In addition, this design has some limitations:
- A scenario in which we would need to run several versions of the game (v1, v2, v3, etc) simultaneously is likely, and these versions may be forwards-compatible. Using our current schema, this would mean creating a new table or even database for all the new versions.
- As is, the schema does not allow for n:m relationships between Players and objects. In case two users were to roll identical pieces of equipment (which is likely, given the algorithms in place), the row would be duplicated. The changes required to make this work are relatively minor.
- Consumables and Ammo are hardcoded into the application.
- This design does not allow for misc. items, like quest items and the likes.
I have thought of a new "denormalized" schema in which all objects are stored in a Warehouse table. In this Warehouse table, we would have an UUID for the object and/or maybe a hash field and, since we are using PostgreSQL, a JSONB field with the information about the object. Said information will be mostly read-only, requiring an update only in some rare cases; in addition, searches using data present in these fields will not only be rare, but only performed in some small subsets of data (finding all the Weapons owned by a player, for example, would filter first by Player). This denormalization would not create redundant data in any case, just data coupling. Furthermore, the JSON documents should never be bigger than 2kb, and they would never have nested objects, just one-dimensional arrays at worst.
I've asked some colleagues and they said it didn't sound like a good idea, but couldn't pinpoint exactly why. As far as I know, this isn't any worse than using MongoDB; in fact, if I were to start using MongoDB, I would have to denormalize much harder than I am doing right now.
Is this schema that bad of an idea? Should I keep the current design? What are the reasons against coupling different types of JSON schemas in the same column, other than not being able to perform data validation DB-side (which we should be doing on the server, anyway)?
EDIT: Adding some more context to this question.
- Right now, there is a working client for the game. Since it's still in an internal testing stage, the client directly performs the queries against the database.
- I was hired to program the server and design the REST API for the release version of the game. I've been given pretty much free reign designing the server architecture.
- There is another programmer in the project, but he is working with the client. Communication between server and client will be exclusively done via the REST API.
- We will be using Node.js and PostgreSQL for the server because it's what I'm more comfortable with. I can't foresee whether we will be changing the system in the future.
- We are using an in-house ORM. It wouldn't be a problem to modify the ORM in case we found a limitation.
- Data from/to the API will be sent and received in JSON format.
- A very hastily made estimate of the maximum concurrent number of players, based on wild especulation using a single sample of a similar platform, is 2000.
- I could ask the other programmer to modify the way his app works (like adding cache and the likes), but I'd rather not bother him that much right now. Consider the app sees the server as a black box.
- The proof of concept DB schema is using TPC right now. I had considered TPH (and that's more or less why I thought using JSONB fields would be cleaner; at best I would save a bit of disk space, but the DB still couldn't guarantee the given object is a valid object). Didn't consider TPT but that still doesn't reduce the number of queries, which is why I wanted to use the JSONB schema.
- I also thought about emulating a TPH with a view over the Armors and Weapons table, but we still have the issue of not being flexible enough, and we still don't contemplate the Misc. items table.
- I can't tell much more about it without saying too much about the project, but trust me when I say a scenario in which we would have to run several game versions at once is likely. Export scripts could be used.
- An usecase in which I would need to filter by any of the JSONB's fields is unlikely. Even then, PostgreSQL would allow it with more than acceptable performance, according to most benchmarks.
- An usecase in which I would need to update a single member of a JSONB field is unlikely. Even then, PostgreSQL also allows it.
- An usecase in which I would need to update the whole JSONB field is slightly more likely, but then again, most objects are read-only.
- The given JSON objects would be very simple and relatively small. They would be, at worst, just a key-value pair with arrays filled with primitives.
- Weapons and armor, in the proof of concept, are a n:1 relationship with the player. So to speak, both the instance and the weapon blueprint are the same object. This makes sense, but so would be treating weapon blueprints/archetypes as their own thing and then instancing them in the player's inventory using another table.
- If this were a personal project I wouldn't be asking this question: I would just pick the JSONB option and gladly shoot myself in the foot with it, if only for the learning experience. But this is a project some other people will be using in production, so I'd rather check twice and thrice to commit to a solution.