Polymorphism in databases

Question

I would like to model a database entity for a set of players. Each player should have :

• a number of fixed fields (name, role, ...)
• a number of variable fields for skill levels (if role is ATK, skills should be stat1 and stat2; if role is DEF, skill should be stat3 and stat4).

What is the best way to implement such an entity (both relational and non-relational databases are fine for me)?

The most trivial solution is of course to hold a different table for each role. I also have found this answer, which is nice but is 7 years old and maybe outdated. Other ideas?

---

Here is a sample data set:

"name": "name1"
"role": "attack"
"strength": 10
"constitution": 5

"name": "name2"
"role": "attack"
"strength": 7
"constitution": 7

"name": "name3"
"role": "defense"
"health": 8
"resistence": 8

"name": "name4"
"role": "defense"
"health": 10
"resistence": 10

"name": "name5"
"role": "support"
"mana": 4
"willpower": 3

Uriia

Polymorphism is an object oriented design concept not a database one. Typically, to bridge the gap between a database and application, and make the former appear object oriented, a tool such as Hibernate is used. Could you just show us sample data? – Turne

I have added a simple data set! – Uriia

This looks to me like you can get away with a single user table, and possibly a few other tables for attributes, if needed to normalize the database. – Turne

Answer 1

The OO structure of your data

You have identified several classes in your Character population, that are derived from the abstract role , namely Attack, Defense and Support. Each kind of role has different attributes depending on the class.

So you have clearly an OOP design in your mind and want to implement it in a database. Several design patterns could be used :

• The easiest seems to be the single table inheritance puts all the fields in a single table. These are used/interpreted depending on the concrete role.
• The class table inheritance puts the data related to each role (and the character itself) in a distinct table. This requires a 1:1 relation between the derived class' table (e.g. Defense) and the parent class table (here Character). This seems an overkill here
• The concrete table inheritance merges the parent classes with the most derived classes, so you'd end up with a table per role, each having its own name field. Again, this seems an overkill here.

Classes or relations ?

There is another additional model that you could consider. It's a component like design, based on composition (in the SQL schema on relations):

• You would have one character table with an id, name and role
• You would have a property table with the character's id, a property-id (or name) and a value.

This could be advised if you want to be very flexible and creative and invent additional properties (e.g "has weapon A", "has weapon B", "armor strength", etc.). However if you intend to stick relatively closely to the current properties, this would be overkill again.

No-SQL

If you'd like to consider a non relational database, typically a No-SQL database, then you could consider document based databases which are perfectly suited to handle structures similar to the single inheritance table.

If you opt however on component design, then key-value stores could also be a choice, but you'd still have to assemble the pieces. That's the cost of the extra flexibility ;-)

You said polymorphism ?

Polymorphism is rather on the behavior that is related to the class rather than the data that describes the objects. As it is not question of behavior here, I guess that you'd meant the handling of the different kind of data (so it's more about classes). Let me know if I'm wrong on this point.

You should however let the polymorphism in the question, because it could help other people who are less aware of OOP terminology to find solutions to similar problems

Coprolite

Answer 2

To expand on the reference to NoSQL solutions in Cristophe's answer, a polymorphic database is perfect for this kind of use case. This would allow you to natively implement the polymorphic data model without an ORM or integrity managament.

I've put together a quick TypeDB schema that should work for your sample data:

define

player sub entity,
    abstract,
    owns name @key;

attack-player sub player,
    owns strength,
    owns constitution;

defense-player sub player,
    owns health,
    owns resistance;

support-player sub player,
    owns mana,
    owns willpower;

name sub attribute, value string;
skill sub attribute, abstract, value long;
strength sub skill;
constitution sub skill;
health sub skill;
resistance sub skill;
mana sub skill;
willpower sub skill;

Here I've defined an entity hierarchy, with each of the player roles attack-player, defense-player, and support-player extending an abstract player supertype. I've also defined an attribute hierarchy, so all of the skills extend an abstract skill supertype. Each player role owns different skills as needed, and they all inherit name from player.

Using this schema, your sample data can be inserted as follows:

insert

$player-1 isa attack-player,
    has name "name1",
    has strength 10,
    has constitution 5;

$player-2 isa attack-player,
    has name "name2",
    has strength 7,
    has constitution 7;

$player-3 isa defense-player,
    has name "name3",
    has health 8,
    has resistance 8;

$player-4 isa defense-player,
    has name "name4",
    has health 10,
    has resistance 10;

$player-5 isa support-player,
    has name "name5",
    has mana 4,
    has willpower 3;

Now your data can be queried polymorphically. To get the attack skill of "name1", you can use:

match
$player isa player,
    has name "name1";
fetch
$player: attack;

To get all the skills of "name1", use:

match
$player isa player,
    has name "name1";
fetch
$player: skill;

To get defense players and their skills, use:

match
$player isa defense-player;
fetch
$player: name, skill;

To get all players that have a mana skill regardless of their player role, use:

match
$player isa player,
    has mana $mana;
fetch
$player: name, mana;

To get all players and all of their skills, use:

match
$player isa player;
fetch
$player: name, skill;

Disclaimer: I have worked on TypeDB.

Kalynkam