vincent15000's avatar

Games application database design

Hello,

I want to develop an application for treasure hunts.

Each hunt can have several quests and each quest can have several challenges.

A challenge can be any type of game : type a word, multiple choice, unique choice, puzzle, words pairing, localisation, ...

I'm thinking about how I can design the database to store all types of games with the same database tables.

Do you have any ideas how to design the database ? Or perhaps any documentation ?

Thanks for your help.

Vincent

0 likes
15 replies
LaryAI's avatar
LaryAI
AI
4 months ago
Level 58

Certainly! Here’s one approach to designing a flexible database schema for your treasure hunts application, able to handle multiple types of challenges/games.

Concepts

  • Hunts contain many Quests.
  • Each Quest contains many Challenges.
  • Each Challenge is a polymorphic game type (e.g. type a word, multiple choice, etc).

Suggested Tables

hunts

id | name | description | created_at | updated_at

quests

id | hunt_id (FK) | name | order | created_at | updated_at

challenges

id | quest_id (FK) | challenge_type | title | description | order | created_at | updated_at
  • challenge_type stores which game type this is (e.g. "multiple_choice", "pairing", etc).

multiple_choice_challenges

id | challenge_id (FK) | question | created_at | updated_at

multiple_choice_options

id | challenge_id (FK) | option | is_correct | created_at | updated_at

word_input_challenges

id | challenge_id (FK) | target_word | case_sensitive | created_at | updated_at

word_pairing_challenges

id | challenge_id (FK) | prompt | created_at | updated_at

word_pairing_pairs

id | word_pairing_challenge_id (FK) | left_word | right_word | created_at | updated_at

... (Continue for other game-specific tables.)

Why this approach?

  • Polymorphic Table Structure: The challenges table acts as a parent. Every specific game type has a related table for its unique fields. This is called single-table inheritance or table-per-type.
  • You can add new challenge types later without modifying existing tables.

Example Eloquent (Laravel) Model Relations

For example, Challenge model:

public function specific()
{
    switch($this->challenge_type) {
        case 'multiple_choice': return $this->hasOne(MultipleChoiceChallenge::class);
        case 'word_input': return $this->hasOne(WordInputChallenge::class);
        // ...etc
    }
}

Further Reading

If you want to get even more generic, you could also consider JSON fields to store dynamic challenge configuration, but it's usually better to stick to a structured approach as shown above for query-ability and data integrity.

Let me know if you have follow-up questions on any specific game/challenge types!

vincent15000's avatar

Lary's suggestion is to create one table per game type.

Would there be another possible design with the same tables for all games types ?

Glukinho's avatar

One table for all challenges sounds better. Something tells me challenges are almost identical in terms of stored data (players, scores, execution time - they share the same data, right?).

Another thought for having one table is you don't want to create new tables every time new challenge type is implemented, with the same bunch of models, relations, etc.

Take a look at this package, it allows to have different "virtual" models while they all are stored in one table with a column to differentiate: https://github.com/tighten/parental Maybe this is the most suitable solution.

1 like
vincent15000's avatar

Hmmm ... thank you for the tip, I just had a look at this parental package, but I'm not so fan. I prefer rather doing this by myself if it's really what I need.

vincent15000's avatar

I already read your post on database design some months ago, but it doesn't really help me for the moment.

martinbean's avatar

@vincent15000 Take what you’ve written, and extract the nouns:

Each hunt can have several quests and each quest can have several challenges.

Each emboldened word there would be a table. Where you have, “each X can by one of multiple types”, like here:

A challenge can be any type of game : type a word, multiple choice, unique choice, puzzle, words pairing, localisation, ...

That’s the sign you have a polymorphic relation. So you’d have a challenges table, that then has a polymorphic relation pointing to the actual instance of what type of challenge it could be.

1 like
vincent15000's avatar

For the hunts, the quests and the challenges, I have no problem to design the database.

What I don't know is how to design the database for the challenges.

Example of challenge 1 :

  • the challenge is a question

  • the player has to type the answer (a word), for example : SOLUTION

Example of challenge 2 :

  • the challenge is 4 different items

  • the player has to sort 4 items in the right order, for example A, B, C, D

Example of challenge 3 :

  • the challenge is a localization

  • the player has to type the GPS coordinates, for example : 48.866667 in one input, 2.333333 in another input

For each challenge type, I need to store the question / answer in the database, but for each one the data to store are different (different number, different type --- text, image, ... ---, ...) and the answer in for each type not the same (word, array of items in the right order, 2 numbers, ...).

Do you suggest me to have a new table for each new challenge type ? I'm ok for a polymorphic relationship between the models, but what about the database structure ? I don't see any other solution than JSON fields, even if I know that it's not recommended.

martinbean's avatar

@vincent15000 As mentioned, you would have a polymorphic relation in your challenges table if a “challenge” can be one of many different types. The polymorphic relation would then point to a separate table that has a row for that particular challenge type.

So you’d have a separate table for “question” challenges that have question challenge-specific columns; another table for item sorting challenges that again have the columns specific to that type of challenge; and so on.

1 like
vincent15000's avatar

I just wanted to prevent modifying the database if I have to add a new challenge type.

But I understand that it's preferable to have one table per challenge type instead of trying to manage all types in one unique table.

martinbean's avatar

I just wanted to prevent modifying the database if I have to add a new challenge type.

@vincent15000 But if you introduce a new challenge type then you’re going to have to create a table in the database to record the details of those challenges?

1 like
Tray2's avatar

I would probably use a more generic table for this, one that can hold different types of data, and not be so strict about what I store in each column.

Something like.

Questions:

  • question
  • correct_answer_id
  • question_type_id

Answers:

  • position
  • answer
  • answer_type_id
  • question_id
1 like
vincent15000's avatar

Your suggestion seems interesting ... I have to think about how apply it for example for a multiple choice challenge or a geographical coordinates challenge.

martinbean's avatar
martinbean
4 months ago
Best Answer
Level 80

I have to think about how apply it for example for a multiple choice challenge or a geographical coordinates challenge.

@vincent15000 You’re not going to be able to represent wildly different data structures in a single database table without just smashing information in something like a JSON column. This is why I suggested different tables for your different challenge types, and then putting a polymorphic relation in your challenges table.

1 like

Please or to participate in this conversation.