I would like to model a database entity for a set of players. Each player should have :
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
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 :
Defense
) and the parent class table (here Character
). This seems an overkill herename
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):
character
table with an id
, name
and role
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