Is there any advantage/disadvantage of storing field value as a JSON array rather than creating new table and one-to-many relationship bet them?

Question

Suppose each user has some numbers(IDs of props in a web game, e.g.) to store in a DB.

I saw some implementations storing the numbers as a JSON string (representing an Array structure) in one field of the user's info table. But my intution is creating another table like below

CREATE TABLE user_numbers (
    userid INT,
    user_number,
    FOREIGN KEY (userid) REFERENCES user_info(id) ON DELETE CASCADE
);

as it's the formal way for a one-to-many relationship.

So I'm wondering does the JSON methods has any practical benefits or it's just a personal choice?

Answer 1

In my experience that depends pretty much on the data which is stored. Both ways have advantages and disadvantages. If it's a MMORPG webgame then say you have a PC that has a belt. And the PC may put potions in that belt for a quick access during a battle. So we want to save the ids of the potions, that are stored in the character's belt.

The most common request would be "get all potions that the character X has". And that would run pretty fast in both cases.

The benefits of storing these potion-ids as a separate table:

• You are able to search for a specific potion-id and it's very fast. In-game-example: admins have removed some potion from the game and therefore you need to update everyone's belts
• You can get some nice statistics. In-game-example: look for the most used potion among all players
• The database will maintain the data integrity. In-game-example: you will never encounter a situation when you used the potion and the game says "Oops, a potion with that id does not exist"
• It is good for consistency. In-game-example: you took a potion from the belt and put it into the backpack. The game can implement it by calling a transaction with two simple clear SQL statements.
• You can do JOINs. In-game-example: we need to get a list of potions in the belt along with their names, weights, and images which are stored in the separate table.
• You can update a single item, without needing to update the whole belt. In-game-forced-example: you have a million potions in you belt and you drank one.

The benefits of storing as a json:

• If it's a browser game that uses javascript on the client side, then you get the Belt json object with one simple request instead of doing Select-query and then converting to json
• It is much easier to maintain order of items, since json arrays are ordered already. With the table-approach you would need additional column called "order" and update it every time and check if two items do not have the same order etc.
• You can do a bunch of rearrangement in the Belt on the client side, then click "Apply" — boom, with one query you can update the whole belt. Whereas with the table-approach you would need at least two queries for that (DELETE + INSERT)
• besides, popular DBMS have plugins that support json functions in the database

Bottom line: those are not major advantages and not critical problems. All of them are solvable and with a proper design of the application both solutions will work ok. Before deciding how to store the data, ask youself, what are the most common use-cases for these data and chose the solution afterwards.