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:
So we will need three entities,
RaceClass
,RaceDeity
, andClassDeity
. 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 forRace
,Class
, andDeity
are valid.In diagram form:![enter image description here](https://i.stack.imgur.com/n2aFo.png)
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, 'Jim the Somewhat Strong','HUM','WAR','GODA')
versus(1, 'Jim the Somewhat Strong', 2, 3, 1)
.1
could mean Thief or Elf or GodB, transpositions are impossible/very unlikely with a human readable codes.