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 .

@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.

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.

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.