Databasing Best Practices

So I'm not formally trained in computer science, so I don't know a lot of these things that one would normally be taught in a computer science course, so forgive me if this is a 101 level question.

I'm revamping how SmaugFUSS saves everything (A big project, I must say) here is what I'm wanting to accomplish:

o Robust and well tested database
o Admins must be able to edit MUD data through the MUD website (Editing accounts, player files, areas (objects, mobs, and rooms), etc)

To accomplish this I have the following design:

o MySQL acting as a middle-man between the MUD and the web server.
o JSON to represent actual player files, which are saved in MySQL fields instead of flat files
- IE, a player file is a row in the player_files database, with fields "name", "data", "pcdata", "skills", "objects". Each of these fields are text-fields with JSON data.

The MUD communicates with the MySQL daemon whenever it wants to save/load something. Admins are able to administer the MUD without being logged into the MUD. Players are able to administer their accounts, create new players, etc, through the website. When I add in a forum to the MUD I will be able to directly link MUD accounts and players with forum accounts/identities.

Good news is it's absolutely not 101 level stuff, though you'd be a lot better off as a new coder if it were. The vast majority of what you're suggesting is very nontrivial and would frankly be easier to accomplish designing the code from scratch since that's very nearly what's going to happen anyway for large portions of the code. Failing that, I'd suggest starting with a codebase that already incorporates as many of those features as possible.

The MySQL concept is sound enough. The private WoW servers use MySQL and they are supporting quite a complex code base.

I don't agree with stuffing JSON data into the database though. Why do that? It's just a level of indirection you don't need.

You need to read up more on how SQL works. It will be much easier to access the database if you can do direct queries. For example, find every record with a certain mob name, or every mob in an area.

If everything is in JSON format, inside the SQL you will always have to read the entire database, just to find a single record, which you could easily do in SQL by making it indexed (eg. names).

So the issue I ran into with straight MySQL was with things like skills, objects, etc being saved to a character. Would it be better to have a table called "learned_skills" or something and have it list out every skill that every player knows, then on player load select all learned skills by name?

I originally had the player_files table with all of the save data in different fields. The reason I moved to JSON inside the player table was that it allowed me to simply list all skills that a player knows in one JSON table. Or is this something I should mix-and-match?

The standard SQL way of doing that is this:

• Have a table for players
• Have a table of skills
• Have a cross-reference table that says if a certain player has a certain skill (since this would be many-to-many, that is many players might have one skill, and the one skill might be had by many players)

Then you do a "join" operation to find, for a particular player what skills they have. (That is, join the player table to the cross-reference player on the player ID).

This is very, very common.

See my page here: http://www.gammon.com.au/sql

Scroll down to joins, and in particular "many to many".

Awesome, thanks so much.