What would a schema look like for setting up tables for RPG character creation restrictions

schema

I'm trying to figure out how to layout tables for an RPG database where information such as character race, class, and deity have restrictions on what combination is permissible.

For example:

Race
-----------
id | name
1  | Human
2  | Elf
3  | Dwarf

Class
-----------
id | name
1  | Warrior
2  | Wizard
3  | Thief

Deity
-----------
id | name
1  | GodA
2  | GodB
3  | GodC

I want to limit certain combinations such as only humans and elves can be wizards, only humans and dwarves can be warriors, but all three can be thieves. But I also want to restrict the deity to certain race/class combinations. For example, humans can only worship GodA or GodB but NOT GodC; choosing to be a thief means you can't worship GodB so you end up only being able to choose GodA, etc.

What kind of schema can be used to map this information, is there a name for this type of relational data, and what would a query look like to retrieve the final mapping?

Best Answer

This is a fairly standard modeling exercise, all you are doing is laying out the relationships that will provide valid selections for each character (and as a benefit populate drop-down menus on the front end).

Based on your descriptions, we have the following dependencies:

Race -> Class
Race -> Deity
Class -> Deity

So we will need three entities, RaceClass, RaceDeity, and ClassDeity. The primary key will be a composite of the primary key of each parent entity.

The Character entity will have a FK relationship to those three entities to ensure the values for Race, Class, and Deity are valid.

In diagram form: enter image description here

A few notes:

For tables with a limited number of values (often with common/easily understood abbreviations), it makes sense to use a short character value for the primary key instead of an auto-incrementing integer. This provides us with a few advantages:

  1. Easier to program, simpler queries, no need to join back to reference tables with values that are easily understood. (1, 'Jim the Somewhat Strong','HUM','WAR','GODA') versus (1, 'Jim the Somewhat Strong', 2, 3, 1).
  2. Less chance of mistakes as there is no overlap in key values, e.g. 1 could mean Thief or Elf or GodB, transpositions are impossible/very unlikely with a human readable codes.
Related Question