You know what they say:
"Good decisions come from experience. Experience comes from bad decisions."
If you just want to write it as a learning exercise, feel free to use whatever ORM you want. It'll probably run fine at small scales. There are plenty of other things you also need to learn to build an MMO-like system, so perhaps this isn't the time when you learn to talk SQL directly, and you can save that for some other project.
Good luck!
After diving down the rabbit hole a little further I am starting to think I may need to drastically rethink this.
My current design is to have Skills that are earned by doing tasks associated with those skills (eg. WoW's original weapons leveling system, or SWG's skill system) And as you gain XP for that skill you can buy and unlock other skills. Each skill will grant new abilities, and each ability can have any number of effects.
I don't know if creating junction tables for all these things is a good idea. I can see needing a query for the player character, their skills, their abilities, and finally the effects related to those abilities. It seems excessive. Perhaps storing the Skills, Abilities, and Effects in a flat file and loading it at runtime is a solution. My only concern is memory usage. Once I start adding more to each of those how soon before it gets to out of hand? I think some tests are in order.
First, doing a complex query is not a problem providing you understand how to perform that query and it's not being performed super-often. Whatever DB structure you can come up with for your game, there are 100s of businesses that do more complex queries 10x as often as you would, and it works fine.
Second, you don't need to be doing most database queries during gameplay anyway. You can do them at load time. The chance of you having too many skills and abilities to fit in memory is zero.
One thing that hasn't been mentioned much is indexes. A good index setup will make normalized data very easy to join together, so your queries across junctions won't be particularly slow, even with thousands of skills per character.
A good index setup will make normalized data very easy to join together, so your queries across junctions won't be particularly slow, even with thousands of skills per character.
A thousand-way self-join, versus fetching a single row with a thousand columns, is probably a measurable difference. The main reason being that the index for the table of a thousand skills per player will be a lot "taller" and thus blow out more block cache. (I presume that the primary key on that table would be playerid, skillid, so it would be a contiguous read at least -- if you get that wrong, such as skillid, payerid, then you're in for a world of hurt as you grow.)
At the point where you legitimately have thousands of skills allocated to a given player, I'd actually recommend denormalization and just stashing the IDs of skills you have in a bit vector or something.
The key there is to be mindful of your tools and tech.
Too many people equate "data" with "RDBMS". There are many other ways to store and process data. Some data is a great fit with relationships and queries that work with a relationship database, others are a terrible fit and need a different data system.
I don't plan on having thousands of skills per player. Something closer to 100 at the most.
Each skill costs points as well as XP related to that skill. There will be a fixed number of points available, with higher ranks costing more.
I may end up serializing the player skills every X milliseconds and storing it to the DB.
I may end up serializing the player skills every X milliseconds and storing it to the DB.
Presumably, skills only increment at discrete times, not continually during gameplay. You only really need to do that when skills actually change. You could use a dirty-bit on your in-memory skill values, and a dirty-bit tree for all pieces of your character, to know whether the character needs to be saved, and if so, what parts.
At one point, I think it would be healthy to experiment with a different approach.
Just store accounts and characters in a key/value store, currenlty my favorite storage is Riak.
Give new accounts and character a unique id to use as keys, uuid is a good option (https://en.wikipedia.org/wiki/Universally_unique_identifier). Then store all information bound to the account or character as the value in the structured way. My favourite format is JSON.
You can link accounts to characters by having a list of characters uuids in the account data.
Then, instead of making queries to the database, just load up the JSON into the memory of your server, make alterations in memory and occation dump JSON to the database.
It would look something like this:
{
"account": {
"uid": "123e4567-e89b-12d3-a456-426655440000",
"phash": "JDUkTW5mc1E0aU4kWk1UcHBLTjE2eS90SXNVWXMvb2JIbGhkUC5PczgweVhoVHVycEJNVWJBNQ==",
"email_encrypted": "UkNyOXFaajYvanBkaVJHNXVmZnV6THpHR0pSMDVUU2U=",
"email_hash": "17300987ba9482d229e6bc9f3463b4efbdf2564d",
"character_uids": ["522e4867-f89e-13d6-b672-422615122561", "612f2357-d21c-23e4-c152-122665727512"],
"subscription": "bronze"
}
}
Note that texts that contains weird characters is in most cases best to base64 encode, otherwise jsons parsers might not parse correctly. In this example I would recommend passwords to be hashed with SHA-256 or even SHA-512 and encode to base64 encode before storing in the db (I use bcrypt to generate password hashes).
You probably don't want to store people's emails in clear text in your db, so encrypt using a well known method like AES. But now we have the problem that emails probably need to be unique (one email limited to one account), so we can hash it the email and put a secondary index on it (email_hash). This means we could quickly find if an email is already in use in our system.
{
"character": {
"uid": "522e4867-f89e-13d6-b672-422615122561",
"name": "foobar",
"skills": [{
"combat": [{
"1h_sword": 32,
"2h_sword": 12,
"mace": 82,
"shield": 54
}],
"cooking": [{
"pizza": 99,
"soups": 23,
"grilling": 23
}],
"survival": [{
"acrobatics": 56,
"swimming": 23,
"skinning": 52,
"traps": 13
}]
}]
}
}
