Let me preface this post with the obligatory. This is a hobby project not seeking to be the next "wow-killer" Just something I enjoy because I am inquisitive.

That being said, I have been following a series of tutorials by Christian Richards on YouTube. He uses Photon Server and Unity to do some neat things. I am trying to expand upon his architecture and database models.

I am at the point of creating characters and I got sidetracked by a bit. I've been reading up on database techniques and I think I have an idea of where I want to go but am not sure if its the correct direction to go.

Basically, I have a table that stores the player characters containing an ID, name, gender, and other character related things. I wanted to add a junction table to store the players skills as well as the xp they have earned in that skill. It seems that a Many-to-many relationship would be correct for this. As in Skills can be earned by many players, and players can have many skills. I think this is at least one way to go about this issue but am curious of other ways. I have looked at Planeshifts (open sourced  ) database and they do something similar, although it seems it really depends on your gameplay how your database is laid out.

Would this method also be reasonable for storing items in players inventories, achievements players have earned, and other information for things that all players can have?

Also, I was planning on having a SQLite database client-side to store nothing but visual information. Like item stats, achievement info, quest text info and much more. The idea being that the server can send an id and the client can query the database for the proper way to display the object in question. I suppose this is more of a asset management question, but I like the idea of minimizing server bandwidth by sending id's only.

Thanks for your time and responses!

I did something like this for a project on uni. I will post some pseudo code here, I hope it helps

Table Character {
int CharacterID PRIMARY KEY,//More info depending on the game,
int Gen,
//More info depending on the game
}

Table Skill {
int SkillID PRIMARY KEY,
string Name
//More info depending on the game
}

Table Item {
int ItemId,
string Name
//More info depending on the game
}

Table SkillInstance {
int CharacterID,
int SkillID,
int level //If you need this?
//More info depending on the game
FOREIGN KEY(CharacterID) REFERENCES Character(CharacterID),
FOREIGN KEY(SkillID) REFERENCES Skill(SkillID)
}

Table Inventory {
int InventoryID PRIMARY KEY,
int CharacterID,
int Size,
//More info depending on the game
FOREIGN KEY(CharacterID) REFERENCES Character(CharacterID)
}

Table ItemInstance {
int InventoryID,
int ItemId,
int Spot,
FOREIGN KEY(InventoryID) REFERENCES Inventory(InventoryID),
FOREIGN KEY(ItemId) REFERENCES Item(ItemId)

}

I don't know if this is the best, but it works fairly well. We also had quite a high mark for it, so I am not sure if that means anything.

I didn't touch data bases for about 20 years, but there are normal forms for them, ie https://en.wikipedia.org/wiki/Database_normalization

Each level strips away redundancy, and the rules are fairly simple afaik, so just make a set of tables and relations, and then verify for being in each level of normal form, and if not, change it.

From what I remember, you need to go to level 4, but euhm, it's 20 years ago

You client-side information seems static information. Why would you put that in a data base? A simple file or hard-coded table would work too right? Don't use a data base because they exist, use them because they are the right tool for the job.

If the information is not static, how does that information arrive at the client, and how does it stay up-to-date? Is the additional tracking of what the client has worth the reduction in network bandwidth?

(if you send a network packet, sending a slightly longer one isn't much of a deal, especially if you stay within the packet size.)

@NLScotty

That is exactly what I was talking about! That's an amazing way of showing the relationships. If I was at home I would have thrown together the SQL commands for the tables. Also, the comments "depending on the game" I would love to see more stuff like that. Just a general guideline.

The only other method I can think of would be serializing the information and save it to character table. Not a fan of that idea for several reasons though.

@Alberth

Thanks for that link, I'll be sure to read it this evening! As far as client-side databases it was more for ease of content creation. I know I could just as "easily" build tools to edit a csv or xml file, and that doesn't sound like a bad idea. Perhaps I was so wrapped up in the server side stuff that I got a bit excited and ahead of myself.

I guess my main goal with the client side database is ease of inserts. I could live deploy a new item to players by sending a simple server response on login. Not that I couldn't do this with flat files. Again I may have been a bit database happy .

There are three ways of doing this, each with benefits/draw-backs.

The most straightforward way is to create a table, indexed by "character ID," which contains the skill level for each skill -- a column per skill. This has good performance, makes it easy to analyze, and is straightforward to marshal to/from internal data structures. The draw-back is that each game design change (new skill, remove skill, etc) requires a schema change (add/remove/change columns.)

The second is to create a multi-join table; character ID -> skill ID -> skill level. This makes it easy to add more skills, without a schema change. It makes it slightly harder to marshal to/from game structures (you may or may not get all the skills, may get unknown skills, etc.) Indices get taller, performance slightly slower, etc. But it's quite flexible.

The third is to create a table with "character ID -> JSON blob" and leave schema management to your code marshaling to/from your JSON data. This is super flexible, and has only one row per character, but loses all the other benefits of a database -- you can't easily query "what's the average archery skill level of all players" and such, at least not without a table scan and expensive JSON parsing expression.

Which one do you go with? Depends on your goals. But don't underestimate the simplicity of one-row-per-character, one-column-per-skill!

@hplus0603

I have given all three of these some thought.

I agree with every point you made, and they represent mostly how I feel on each.

I want my solution to be as flexible as possible to allow me to play with many different kinds of game systems, while maintaining a standard. The first solution of column-per-skill seems pretty far from 1st normalized form as I understand it (thanks @Alberth!). Although it would be easy to implement, I agree with your comment about updating column schema's.

The difference between the second and third options (junction tables and serialized data) aside from what you've stated, seems to be do I want the database and ORM to handle the conversion, or my server logic. I think as a hobbyist programmer I would much rather have software written by others do the 'heavy lifting'.

After reading through the link (and diving down the wiki-hole if you will) I think that the junction tables are the closest to 1NF. So it seems I have made up my mind.

As for client side data storage I am still on the fence. I like the idea that a SQLite database abstracts the data by at least one level. I realize that it wouldn't protect it against someone hell bent on changing it, but it certainly wouldn't be as simple as editing a CSV. Also, in terms of maintainability, it seems like a database would be ideal. Tables with clear names, and the ability to easily find, insert, or remove data. But I must admit this would all be possible using a flat file (thanks cntl+f haha). And realistically both solutions would need to at least partially be loaded into memory for the duration of the applications process. Still really not sure on this one.

Quote

 

The first solution of column-per-skill seems pretty far from 1st normalized form as I understand it

 

It's totally normal, if you consider a character as being an entity with a well-defined set of skills.

If you believe that skills are separate entities unto themselves, and they come and go (rather than the character just raising/lowering/enabling/disabling them) then, yes, you need a separate table to join characters to skills.

I would recommend that you do NOT go for flexibility right now, because making just your first game is going to be hard work enough -- you're not going to have lots of time to try many different things, at least not to the point where you will put them into the database. That being said, if you really believe this is an area of quick iteration, then just slam 'em all into a single JSON blob and call it good!

Separately, I would advise NOT to use an ORM. Ever. For any application. Objects in RAM behave differently from rows in a database, and the impedance mis-match that happens when you try to join them, ends up killing projects. And, what's most insidious, is that this kind of death only happens towards the end of the project, where scale makes itself known. Projects that manage to pull out of this, typically re-code their ORM integration in raw SQL, and exactly how painful that is, depends on how heavily they relied on the ORM "magic" before then.

http://blogs.tedneward.com/post/the-vietnam-of-computer-science/

https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/

http://seldo.com/weblog/2011/08/11/orm_is_an_antipattern

Programming with relations in memory is actually, in general, a better model than programming in "rich" objects, anyway -- this is the learning from the "data oriented design" movement that's basically taking over high-performance gamedev in the last 15 years or so.

Advice from someone who had to write the database schema and data migration tool for a game that did not plan for database schema migration - plan for schema migration.

The data you choose to store will change. Extra rows of data are mostly easy as the only significant clashes are your primary keys. Make sure you don't have tables that gain new rows during design and play - for example, if players can invent new object types, but you/your designers can too, you may have problems merging those 2 tables when you deploy the new item types to the live database. So when using a relational DB for MMO data I designate some tables as read-only during play - these are the ones designers can add data to. Often the data model needs adjusting accordingly.

Changing table columns typically requires some sort of explicit migration strategy. Adding a column is simple enough, providing you have a default value specified. Deleting a column is also fairly simple as long as nothing refers to it. Changing the type of a column, or expanding a column out into multiple columns, or maybe changing it into a reference to a new table - that gets awkward. Look into tools that will manage this for you. Usually you have to write an upgrade script (sometimes aided by some automated process that fills in the basics for you) and an accompanying downgrade script so that you have a one-command approach to changing database versions, and reverting in the event of problems.

Another way to bypass all of this is by using BLOBs or JSON fields, and have the schema entirely inside the application, writing the whole object into one column. Any migration can be performed when the object is loaded, and a full migration is just a case of reading and writing every object. Personally I like this method because game database schema change a lot during development and being able to manage it in one place within the code is more amenable to fast iteration of new features. The downsides are that you lose a lot of the usual benefits of relational DBs, such as a well-known query language, plenty of mature tooling, etc.

SQLite is a reasonable format for client-side data if you know it fits the relational model. Given that you're usually using it as a read-only data source you don't have to worry about the migration issues.

Adding a column is simple enough

The simple tools to "alter table add column" will lock the table for the duration of updating the data on disk, at least with MySQL. For heavily used tables, this is a non-starter, unless you're OK with hours of downtime.

There are tools that alter the schema "online" by re-writing the data in the background; we have tables that are big enough that this work never completes and finally declares failure after days of trying.

So, adding columns on small tables (say, below ten million rows and below a gigabyte in size) is quite doable; once you get bigger than that, nothing becomes simple anymore :-)

Then the second question is: How much do you need to worry about this? 99.99% of games never get that big. Solving that problem too early, means you're wasting time solving a problem that you don't need to, which is time that you could have used to work on more immediate problems, like "is the game fun?" and "can you actually get players?"

(Almost) Every successful system is full of shortcuts, that have to be solved at some time in the future. Knowing where a little bit of extra engineering will save you lots of time later, and ideally also make you faster while building that future, is the trick. Over-engineering is not the solution!

This is exactly why I came here for help! Thank you @hplus0603 and @Kylotan

I don't know how I feel about going forward without using NHIbernate. For me its about ease of use, and I really never plan for this project to have the Massively part of MMO. I do wonder if I'm overthinking this, as I often do.

I don't want to seem like I am discounting the ease of multi-column tables, or JSON blobs. I am just not sold on them.

I think the bigger issue for me know is going from ORM to no-ORM. I read the articles your posted @hplus0603 and they're hard to argue with.

The real lesson here is I need to learn how/why to make these decisions lol.