Player to class database relationship in a game

Question

I'm attempting to figure out how to model a normalized database where a player may have multiple classes. What is the relationship between the player and the class objects?

Each player will be able to have multiple classes and each class will then grant a player multiple skills as well. I'm familiar with normalized database and working with them, but haven't set up a lot of them from scratch, especially not for myself.

I'm working with the MySQL community toolset if anyone has a handy EER diagram/screenshot they can show me as well :)

Answer 1

This would normally be handled with a link table for a many-to-many relationship:

PlayerClass:
PlayerID
ClassID

Usually with PlayerID, ClassID being the primary key, and PlayerID and ClassID being foreign key references to your Class and Player tables.

Typically this will be indexed the other way as well (ClassID, PlayerID) in many designs (although possibly not in yours).

Your skills table would be similarly linked to a class:

ClassSkills:
ClassID
SkillID

Now here's where it gets tricky - what happens if a player is in two classes which each confer the same skill? Are they additive or are they merged, or what? This is a problem domain issue which would have to be resolved before you can simply join these tables all together and see what skills a player has.

Answer 2

The relationship would be many to many. Usually modeled by an in-between table:

table Player:       Id int, name varchar(50)
table PlayerClass:  PlayerId int, ClassId int
table Class:        Id int, name varchar(50)

The PlayerClass table should have a foreign key constraint on both its columns.

Note that databases are considered too slow for games. Most games use a custom storage mechanism.