Database design question

The players of the game have to complete missions, which they earn some experience.

My doubt is which database structure for calculating the user experience i should choose:

  1. Add the missions completed together with the respective experience earned to a data set, and when the players want to know their level, sum all the missions’s experience and make the calculations to know the level.

Or

  1. Increase a single variable called experience so that “experience = experience + earned”

What i think so far is that the 1. may be heavier to process and make the database bigger, and 2. may be not as secure and reliable.

I want to know your opinion in which structure is better for this circumstance,
thanks

Generally speaking, I would always encourage a ledger approach (approach #1) over a single value (approach #2).

With a ledger-based approach, you’re able to capture significantly more data about an event, making your approach more dynamic (ie. improvement in the last week, gain versus loss, etc). However, one incorrectly structured workflow or reference, and you could end up erasing all history of that player.

Bare in mind, the rate of game play can be a major factor. Practically speaking, if you generate 200 database entries per player per minute (ie. for every time they click the mouse), you’ll soon have an unwieldy database. However, if it’s capturing events much less frequently, then you’re in a much better position).

For security, so long as you define adequate privacy roles, that shouldn’t be a major issue.

Dan (creator of LearnTo)

1 Like