Notice: Any messages purporting to come from this site telling you that your password has expired, or that you need to verify your details, confirm your email, resolve issues, making threats, or asking for money, are
spam. We do not email users with any such messages. If you have lost your password you can obtain a new one by using the
password reset link.
Entire forum
➜ MUDs
➜ MUD Design Concepts
➜ Databasing Best Practices
|
Databasing Best Practices
|
Posting of new messages is disabled at present.
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 (23,122 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 (23,122 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.
21,989 views.
Posting of new messages is disabled at present.
Refresh page
![[Go to top]](/images/gototop.gif)
top