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

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? — Tim Biegeleisen, Nov 15 '16 at 10:48
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. — Tim Biegeleisen, Nov 15 '16 at 11:27

Christophe · Accepted Answer · 2016-11-20T15:27:36.487

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

Polymorphism in databases

1 Answers1