Database Options
Goto page 1, 2  Next
 
Post new topic   Reply to topic    mudlab.org Forum Index -> Coding
View previous topic :: View next topic  
Author Message
Stormy



Joined: 09 May 2007
Posts: 6
Location: Northern Virginia

PostPosted: Sun May 20, 2007 8:22 am    Post subject: Database Options Reply with quote

To start, I've read the other database-related threads in this forum, but I couldn't derive a solution from them (or I just didn't understand something enough to derive a solution). I'm writing a MUD in Ruby, and I need to start storing data. Initially, I was planning on using MySQL and saving via a threaded queue to the database (when an object's turn came, it would commit itself if it's a string, or call its save method). However, I realized that my approach involved manually adding new fields to the database each time I added a new variable that I wanted to save. This seems annoying.

I need to store game objects (accounts, players, locations, etc) as well as commands, helpfiles, and everything else. I plan to handle involved-OLC (world, npcs, objects, etc) via a web interface. Any suggestions?

Thanks for any feedback.
Back to top
View user's profile Send private message
Author Message
Itansha



Joined: 20 May 2007
Posts: 4

PostPosted: Sun May 20, 2007 8:20 pm    Post subject: Re: Database Options Reply with quote

Stormy wrote:
However, I realized that my approach involved manually adding new fields to the database each time I added a new variable that I wanted to save. This seems annoying.


You might want to serialize (either using Marshal or YAML) to a blob field and only have database fields for common attributes that you wish to search against or reference in SQL queries.
Back to top
View user's profile Send private message
Author Message
Stormy



Joined: 09 May 2007
Posts: 6
Location: Northern Virginia

PostPosted: Tue May 22, 2007 3:44 am    Post subject: Re: Database Options Reply with quote

Itansha wrote:
You might want to serialize (either using Marshal or YAML) to a blob field and only have database fields for common attributes that you wish to search against or reference in SQL queries.


Thanks for the reply. I saw that method in one of the other threads as well -- I'm not sure how fond I am of it though, because I'd lose direct access to a lot of information. I started looking at the Og gem from the Nitro project today, which I think may be just what I'm looking for, and I think it would work well for a MUD. Does anyone have experience with Og that could comment on the experience and why it would or wouldn't work out?
Back to top
View user's profile Send private message
Author Message
Itansha



Joined: 20 May 2007
Posts: 4

PostPosted: Tue May 22, 2007 5:12 am    Post subject: Re: Database Options Reply with quote

Stormy wrote:
Thanks for the reply. I saw that method in one of the other threads as well -- I'm not sure how fond I am of it though, because I'd lose direct access to a lot of information. I started looking at the Og gem from the Nitro project today, which I think may be just what I'm looking for, and I think it would work well for a MUD. Does anyone have experience with Og that could comment on the experience and why it would or wouldn't work out?


No problem. I don't have direct experience with Og, but it looks pretty spiffy, and I agree that it appears that it would work well for a mud. If you're worried about losing direct access to information, though, you should know that when it stores Hash and Array objects as properties of an object, it serializes them using YAML into a string field. Overall, though, it would expose the majority of the object to direct access.

I know that you specified MySQL as the database, but you also might want to check out PostgreSQL because it has some neat features that might fit what you're trying to do. Specifically, it has table inheritance as well as native arrays, composite, and domain datatypes, among others.
Back to top
View user's profile Send private message
Author Message
Aioros



Joined: 16 Jun 2005
Posts: 14
Location: Lisboa, Portugal

PostPosted: Tue May 22, 2007 1:55 pm    Post subject: Reply with quote

How about you store one attribute per record, instead of having one record for the whole player/object/room/whatever?

For instance ...

You have a table with the columns (all strings) char_name, attribute_name, attribute_type and value (primary key would be char_name and attribute_name).

So my char would consist of several records in database:

"Aioros", "Hp", "Integer", "1000"
"Aioros", "Mana", "Integer", "800"
"Aioros", "Armor", "Integer", "500"
"Aioros", "Class", "String", "Cleric"
...

You can convert string values to their ingame types using the type column, and you don't need to change table structure every time you need to add an attribute.
Back to top
View user's profile Send private message
Author Message
gerund



Joined: 24 Nov 2005
Posts: 14

PostPosted: Thu May 31, 2007 5:45 pm    Post subject: Reply with quote

Aioros wrote:
How about you store one attribute per record, instead of having one record for the whole player/object/room/whatever?


This is how it is done in EVE Online. We also have a web based interface which all game design, game mastering and general live game state browsing/modification is done within which we call ESP (EVE server pages).

We initially started off when we built one of the intermediate prototypes of the game by serialising the complete state for objects (using Python's pickle module). Like the character's state and the state for a ship which the character might own.

The pickling was ditched as a matter of course once we got a database server hooked up. However, where we have pickled blobs into the database for certain features (other programmers than I) it has resulted in confusion and pain. The problem encountered was that it made the saved state dependent on the implementation of the object, which is definitely something to avoid. Since the programmer who did it left the company, the next programmer who inherited the system proceeded to make changes which were incompatible with saved state which was then erroring when unpersisted. It was very difficult to track down, considering that only a few rare persisted blobs were incompatible.

Our objects are authored by content with values in this manner:

typeID, attributeID, valueInt, valueFloat

And attributes are defined in this manner:

attributeID, attributeName

And values for object instances are persisted in this manner:

itemID, attributeID, valueInt, valueFloat

We started off with valueString as well. But it turned out we had no need for it, so it was dropped along the way. In a text MUD I can see that this would of course not be the case.

If there is one thing I could change about this in retrospect, it would be to create some manner in which a set of attribute values could be defined as a group. And then rather than having to set all of those values on a type, you could just associate the attribute value sets with the type. This achieves several things. It reduces the work which the content department has to do to author new types and maintain existing types where all these attributes had been authored in a similar manner. But the key reason it has been discussed is that the sheer number of types and attribute values defined for types in EVE Online makes for a huge amount of static data which has to be sent to every client which connects. I personally care less about the latter reason and more about the former.

Another area discussed where it has been appealing to allow the content department to author sets of attributes for types, but in a different manner, was for the scenarios they author. The problem here was that if they wanted an existing type to be spawned, but with some type attributes changed, they needed to copy the type and modify those attributes. By allowing spawning of types but with overriding sets of attributes (something which wouldn't be too hard to implement for EVE but hasn't been done), this removes that need to copy.

Anyway, just felt like throwing out my thoughts on this. Hope it is of some interest.

-- Gerund.
Back to top
View user's profile Send private message
Author Message
Drey



Joined: 15 May 2005
Posts: 24
Location: Livonia, MI

PostPosted: Fri Jun 01, 2007 2:35 pm    Post subject: Reply with quote

Aioros wrote:
How about you store one attribute per record, instead of having one record for the whole player/object/room/whatever?


Gerund's comments in the previous post not-withstanding, this tends to be inefficient as the number of "objects" in the database grows. It's easier to work with, however, for someone who doesn't want to do good database design.
Back to top
View user's profile Send private message Visit poster's website
Author Message
gerund



Joined: 24 Nov 2005
Posts: 14

PostPosted: Fri Jun 01, 2007 8:34 pm    Post subject: Reply with quote

Drey wrote:
Aioros wrote:
How about you store one attribute per record, instead of having one record for the whole player/object/room/whatever?


Gerund's comments in the previous post not-withstanding, this tends to be inefficient as the number of "objects" in the database grows. It's easier to work with, however, for someone who doesn't want to do good database design.


I don't disagree with either of the things you say, but without elaboration on how exactly they are inefficient in ways which matter or what exactly you consider to be good enough database design and the benefits it would give, it is kind of hard to anyway Smile

Since I am also looking at implementing my own system for a prototype I am writing for an experimental game system, I would appreciate the insight this added detail would give.
Back to top
View user's profile Send private message
Author Message
Drey



Joined: 15 May 2005
Posts: 24
Location: Livonia, MI

PostPosted: Tue Jun 05, 2007 1:35 pm    Post subject: Reply with quote

gerund wrote:
I don't disagree with either of the things you say, but without elaboration on how exactly they are inefficient in ways which matter or what exactly you consider to be good enough database design and the benefits it would give, it is kind of hard to anyway Smile

Since I am also looking at implementing my own system for a prototype I am writing for an experimental game system, I would appreciate the insight this added detail would give.


I work on databases in my professional life and I have a bias towards how they should be used. In my possibly archaic world-view, if you wanted to use a database to store rooms, mobiles, objects, and players -- you'd make a table for each kind of thing you want to store. Any property that every room has would go into it's table or into a child table in the case of things like room flags. Any properties that might exist for only certain kinds of room could go into a name-value table.

When you go for a pure name-value pair model, in order to get back a single room for your database you have to do a number of table joins against the name-value pairs table to get back all of the attributes that make up a room. As the number of rooms increases, so does the number of entries in the name-value pair table which leads to inefficiencies in the data retrieval.
Back to top
View user's profile Send private message Visit poster's website
Author Message
gerund



Joined: 24 Nov 2005
Posts: 14

PostPosted: Tue Jun 05, 2007 3:49 pm    Post subject: Reply with quote

Drey wrote:
I work on databases in my professional life and I have a bias towards how they should be used. In my possibly archaic world-view, if you wanted to use a database to store rooms, mobiles, objects, and players -- you'd make a table for each kind of thing you want to store. Any property that every room has would go into it's table or into a child table in the case of things like room flags. Any properties that might exist for only certain kinds of room could go into a name-value table.

When you go for a pure name-value pair model, in order to get back a single room for your database you have to do a number of table joins against the name-value pairs table to get back all of the attributes that make up a room. As the number of rooms increases, so does the number of entries in the name-value pair table which leads to inefficiencies in the data retrieval.


Thank you for the elaboration. No disagreement here. In our case the inefficiencies with our use of a generic system never become a concern because of the custom ways in which we make use of the data.
Back to top
View user's profile Send private message
Author Message
Kelvin



Joined: 08 Apr 2007
Posts: 10

PostPosted: Sat Jun 09, 2007 6:30 pm    Post subject: Reply with quote

The way Evennia handles this is similar to MUX/MUSH's take on the matter. The standard codebase's database's method of portraying in-game entities is composed of generic Objects that are stored in the same table, which may be one of several types:

  • Player
  • Room
  • Thing
  • Exit

The attributes and properties that are common to all of these object types are stored in this table, things like Name, Description, Location (if applicable), Owner, etc. Anything that is specific to a particular object type, or specified by the admin is stored in an attribute. Attributes are stored in another table with an association back to the object in the Objects table. For example, a player may want to set an attribute on themselves called LIKES_BEER. Rather than having a field in the Object's table, we simple create an entry in the attribute table with a unique numerical id (not visible in-game), a string-based key that players will refer to it by, and a value.

The benefits of this are many:

  • Avoids cumbersome pickling.
  • Allows for searching of attributes values very quickly.
  • Keeps the generic Object model... generic. Having to modify your table structure frequently is indicative of bad DB design.
  • We can add very complex features with lots of attribute usage without modifying our table structure at all, requiring codebase users to apply obnoxious database updates that may or may not work.


As far as some of the suggestions for separate tables for rooms/exits/things, IMHO this is a horrible idea and is part of why I can't stand Diku-derivatives. The concept of shared VNUMs is awful. It's completely counter-intuitive to have an exit whose number is 312, a room whose number is 312, and a player whose number is 312. Under a generic object storage setup, there can only be one #312. If I say, "Take a look at #312", someone else will know exactly what I'm talking about. But that's an entirely different rant.

And finally, regarding performance nit-picking. Keep in mind that custom codebase projects developed these days are likely to never see more than 50-70 people on at any given time. Also keep in mind the state of hardware in the current time. Database design for such small-scale projects should center around ease-of-use and development rather than performance (within reason). It's a lot harder to design low-traffic databases that'll bring a database to its knees these days.
Back to top
View user's profile Send private message
Author Message
Vopisk



Joined: 22 Aug 2005
Posts: 99
Location: Golden Valley, Arizona, USA

PostPosted: Sun Jun 10, 2007 12:18 am    Post subject: Reply with quote

Quote:
As far as some of the suggestions for separate tables for rooms/exits/things, IMHO this is a horrible idea and is part of why I can't stand Diku-derivatives. The concept of shared VNUMs is awful. It's completely counter-intuitive to have an exit whose number is 312, a room whose number is 312, and a player whose number is 312. Under a generic object storage setup, there can only be one #312. If I say, "Take a look at #312", someone else will know exactly what I'm talking about. But that's an entirely different rant.


It's entirely possible to store a separate table that maps all objects contained within the DB as a whole to unique identification numbers. This would allow you to keep tables separate for the sake of sorting out the information say, offline when you just want to review the information in the database, but at the same time, only having a single, numerical object reference point.

-Vopisk

Re-reading, it almost sounds like that was what you were suggesting to begin with. Smile If that's so, I guess we're in agreement that this is probably the best way to do things for logical sanity.
Back to top
View user's profile Send private message AIM Address MSN Messenger
Author Message
Kelvin



Joined: 08 Apr 2007
Posts: 10

PostPosted: Sun Jun 10, 2007 12:23 am    Post subject: Reply with quote

Vopisk wrote:
Quote:
As far as some of the suggestions for separate tables for rooms/exits/things, IMHO this is a horrible idea and is part of why I can't stand Diku-derivatives. The concept of shared VNUMs is awful. It's completely counter-intuitive to have an exit whose number is 312, a room whose number is 312, and a player whose number is 312. Under a generic object storage setup, there can only be one #312. If I say, "Take a look at #312", someone else will know exactly what I'm talking about. But that's an entirely different rant.


It's entirely possible to store a separate table that maps all objects contained within the DB as a whole to unique identification numbers. This would allow you to keep tables separate for the sake of sorting out the information say, offline when you just want to review the information in the database, but at the same time, only having a single, numerical object reference point.

-Vopisk

Re-reading, it almost sounds like that was what you were suggesting to begin with. Smile If that's so, I guess we're in agreement that this is probably the best way to do things for logical sanity.

Well, I agree with unique ID numbers, but I still don't agree that separate tables are needed for sorting, particularly considering there are all kinds of ways to sort and filter with SQL. Instead of looking to one table for everything, you now need to add logic to worry about which table to look at, and possibly perform more sloppy looking joins.
Back to top
View user's profile Send private message
Author Message
Drey



Joined: 15 May 2005
Posts: 24
Location: Livonia, MI

PostPosted: Mon Jun 11, 2007 1:37 pm    Post subject: Reply with quote

Kelvin wrote:
Having to modify your table structure frequently is indicative of bad DB design.


Having to modify your table structure frequently is indicative of having not fully designed your system before beginning coding.

Kelvin wrote:
We can add very complex features with lots of attribute usage without modifying our table structure at all, requiring codebase users to apply obnoxious database updates that may or may not work.


I still can't understand the reasoning that says "it's bad to have to change the database" but "it's OK that we changed the non-database code". While some use of name/value tables can be beneficial, I've seen people take it to the extreme that everything gets stored that way, just because they don't want to make database changes. Yet, they're perfectly content making code changes. This always implies to me that they're not comfortable working with databases.

Kelvin wrote:
Database design for such small-scale projects should center around ease-of-use and development rather than performance (within reason).


This is why software never seems to get faster even though the underlying hardware is; too often performance is being sacrificed for ease of coding anymore. One of the defenses of Java and C# is often "yes, it's slower than compiled code but with the speed of the hardware these days it doesn't matter as much."
Back to top
View user's profile Send private message Visit poster's website
Author Message
Kelvin



Joined: 08 Apr 2007
Posts: 10

PostPosted: Mon Jun 11, 2007 1:53 pm    Post subject: Reply with quote

Drey wrote:
Kelvin wrote:
Having to modify your table structure frequently is indicative of bad DB design.


Having to modify your table structure frequently is indicative of having not fully designed your system before beginning coding.

This is another possible reason, although it can go hand-in-hand with bad design.

Drey wrote:

Kelvin wrote:
We can add very complex features with lots of attribute usage without modifying our table structure at all, requiring codebase users to apply obnoxious database updates that may or may not work.


I still can't understand the reasoning that says "it's bad to have to change the database" but "it's OK that we changed the non-database code". While some use of name/value tables can be beneficial, I've seen people take it to the extreme that everything gets stored that way, just because they don't want to make database changes. Yet, they're perfectly content making code changes. This always implies to me that they're not comfortable working with databases.

I think you may've mis-interpreted me. The big perk here is that you don't need to make changes to the database OR any other lower-level code when using an attribute-based system. You have a generic accessor for the attributes, and you can add new ones on the fly and get/set without having to worry about table structure. For example, here's what my attribute interface methods look like:

Code:

SomeObject.get_attribute("SEX")
SomeObject.set_attribute("SEX", "Male")
SomeObject.has_attribute("SEX")
SomeObject.delete_attribute("SEX")


Particularly in the case of codebases (rather than individual games), developers may want to make it easy for others to strip out things they don't want (such as player sexes), or add new things just as easily (see attributes). So, as you can see, using these three functions is a LOT easier than modifying table structures every time you want to add a field Smile
Drey wrote:

Kelvin wrote:
Database design for such small-scale projects should center around ease-of-use and development rather than performance (within reason).


This is why software never seems to get faster even though the underlying hardware is; too often performance is being sacrificed for ease of coding anymore. One of the defenses of Java and C# is often "yes, it's slower than compiled code but with the speed of the hardware these days it doesn't matter as much."

You're welcome to see the live example for this concept in Evennia. It runs very snappy. There's a difference between writing reasonably efficient code and micro-optimizing. It's simply not necessary to shave off that last bit or shed a handful of CPU cycles anymore if it means making your program harder to read, maintain, or extend. What I've presented here makes complex systems that need to track a lot of data very easy. It's not new, it's what MUX/MUSH do as well.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    mudlab.org Forum Index -> Coding All times are GMT
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2002 phpBB Group
BBTech Template by © 2003-04 MDesign