[Home] [Downloads] [Search] [Help/forum]

Gammon Forum

See www.mushclient.com/spam for dealing with forum spam. Please read the MUSHclient FAQ!

[Folder]  Entire forum
-> [Folder]  MUDs
. -> [Folder]  MUD Design Concepts
. . -> [Subject]  Databasing Best Practices
Home  |  Users  |  Search  |  FAQ
Username:
Register forum user name
Password:
Forgotten password?

Databasing Best Practices

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


Posted by Will Sayin   USA  (17 posts)  [Biography] 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
[Go to top] top

Posted by Meerclar   USA  (733 posts)  [Biography] 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
[Go to top] top

Posted by Nick Gammon   Australia  (21,321 posts)  [Biography] 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
[Go to top] top

Posted by Will Sayin   USA  (17 posts)  [Biography] 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
[Go to top] top

Posted by Nick Gammon   Australia  (21,321 posts)  [Biography] 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
[Go to top] top

Posted by Will Sayin   USA  (17 posts)  [Biography] 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
[Go to top] 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.


2,535 views.

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

Go to topic:           Search the forum


[Go to top] 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.

[Home]


Written by Nick Gammon - 5K   profile for Nick Gammon on Stack Exchange, a network of free, community-driven Q&A sites   Marriage equality

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( https://gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Hosted at FutureQuest]