Gammon Forum : MUDs : MUD Design Concepts : Databasing Best Practices

Log on

Gammon Forum

Entire forum

MUDs

MUD Design Concepts

Databasing Best Practices

Databasing Best Practices

It is now over 60 days since the last post. This thread is closed. Refresh page

Posted by

Will Sayin USA (17 posts)

bio

Date

Mon 27 Jun 2016 10:27 PM (UTC)

Message

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.

Will Sayin
Developer, Legends of the Darkstone
www.darkstonemud.com:5432

top

Posted by

Meerclar USA (733 posts)

bio

Date

Reply #1 on Tue 28 Jun 2016 01:03 AM (UTC)

Message

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.

Meerclar - Lord of Cats
Coder, Builder, and Tormenter of Mortals
Stormbringer: Rebirth
storm-bringer.org:4500
www.storm-bringer.org

top

Posted by

Nick Gammon Australia (22,975 posts)

bio Forum Administrator

Date

Reply #2 on Tue 28 Jun 2016 03:23 AM (UTC)

Message

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

- Nick Gammon

www.gammon.com.au, www.mushclient.com

top

Posted by

Will Sayin USA (17 posts)

bio

Date

Reply #3 on Tue 28 Jun 2016 03:12 PM (UTC)

Message

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?

Will Sayin
Developer, Legends of the Darkstone
www.darkstonemud.com:5432

top

Posted by

Nick Gammon Australia (22,975 posts)

bio Forum Administrator

Date

Reply #4 on Tue 28 Jun 2016 08:12 PM (UTC)

Message

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

- Nick Gammon

www.gammon.com.au, www.mushclient.com

top

Posted by

Will Sayin USA (17 posts)

bio

Date

Reply #5 on Mon 04 Jul 2016 05:13 PM (UTC)

Message

Awesome, thanks so much.

Will Sayin
Developer, Legends of the Darkstone
www.darkstonemud.com:5432

top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

19,709 views.

It is now over 60 days since the last post. This thread is closed. Refresh page

Go to topic: Search the forum

top

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.