06 Mar, 2009, Vassi wrote in the 1st comment:
Votes: 0
The codebase I'm working on, for various reasons, will use databases to maintain its persistence. While I've had a lot of practice using SQL, I've never designed a really complex set of schemas before. I've run into a major sticking point that I'd really like to run by other people that have probably had more practice at it than me. I'll try to be as coherent as possible while outlining the problem, so first I'd like to sketch out some of the background.

Firstly, the codebase is married to a custom client. For this reason, the information (Specifically for lands) is very structured and has a lot of metadata that is not common to most MUDs. I think this is an important point because I want to stress that elegance and complexity is not an issue here, I'm not concerned with lowest common denominator so 'making it easy to trade lands and items' is not really an issue.

To get more to the point, my biggest sticking point right now is items. I'm going to be using a template system for mobs and items. Basically this means that those two things are kept in a master database and instances of them are spawned as necessary. The problem with items is that while the majority of the settings on items will not change from those of the template there is still some metadata associated with them that will need to be maintained on a per-item basis. This metadata includes things like enchantments, player customizations, material information, etc. So while the template outlines a sword, a player may be able to craft a sword from these base settings but be able to supplement their own material and then add enchantments, etc. To tie off the issue, item permanence must be maintained in the following places:

Player Inventory
Player Bank
Guild Bank
Individual Rooms

I'm thinking then that I'll need to keep all spawned instances of an object in their own table, and then reference them via their IDs in the appropriate places via some kind of delimited list. Does that seem reasonable? I once attempted to keep them by serializing the data and keeping that but serializing and deserializing the data back and forth led to corruption with unacceptable frequency. To be fair, I tried to 'cut corners' by making it a binary serialization, I did not use a more agnostic method such as XML or KV pairs.

At the risk of annoying the readers and not getting any responses, I'll go on. I mentioned banks earlier, so I have another design point that I'm not sure how to address. Ideally there would be different banks in the game, which is to say the bank in one area would not let you access the items from a different area's bank. The question here is, should the player table (and guild table) be the owners of the 'bank' field, or should the banks be tables of their own. One way that would work would be to let each row have a field for player id, bank id, and then the delimited list of item instances.

I have another design issue with lands but I'll let that one be for now, I don't want to inundate these forums with topics that people may not care a lick about. However I warn you that if I get useful replies I'll post it at a late time.
06 Mar, 2009, David Haley wrote in the 2nd comment:
Votes: 0
Vassi said:
I'm thinking then that I'll need to keep all spawned instances of an object in their own table, and then reference them via their IDs in the appropriate places via some kind of delimited list.

You can store only those objects that have been modified, and after that, store only one copy of the modification, and then an item reference becomes a link to either the template table, or the modified template table. No need to store the full information for each instance if a lot of it will be the same.

You could store inventory as either some kind of list you maintain, or a binary relationship of container to contained. Then to find what a container contains, "select contained_id from contained_relation where container_id = XYZ" – or something like that. The latter's advantage is that it's all in the DB, and in particular the semantically meaningful concept of the list is in the database. The former's advantage is that it's all in one spot, but the semantics now depend now only on the DB but on your interpretation of the fields.

I think the bank problem can be solved with a triplet of container_id, owner_id, contained_id.

Vassi said:
To be fair, I tried to 'cut corners' by making it a binary serialization, I did not use a more agnostic method such as XML or KV pairs.

Direct binary serialization has this kind of problem no matter what other system you're using, so I don't think it's really a pro or con of a database approach here.
06 Mar, 2009, Vassi wrote in the 3rd comment:
Votes: 0
David Haley said:
You can store only those objects that have been modified, and after that, store only one copy of the modification, and then an item reference becomes a link to either the template table, or the modified template table. No need to store the full information for each instance if a lot of it will be the same.


Unfortunately I'm not sure if the latter would work. For instance, tracking item condition introduces 200 possible values by itself (from -100 to 100). For the former, because certainly not all items will be modified, how would I tell the difference between a template ID and an instance ID? I suppose I could choose a cutoff and say 1-9999 are templates and anything after is an instance, but a small part of me doesn't want to make a cutoff if I can avoid it.

Quote
You could store inventory as either some kind of list you maintain, or a binary relationship of container to contained. Then to find what a container contains, "select contained_id from contained_relation where container_id = XYZ" – or something like that. The latter's advantage is that it's all in the DB, and in particular the semantically meaningful concept of the list is in the database. The former's advantage is that it's all in one spot, but the semantics now depend now only on the DB but on your interpretation of the fields.


I will admit I'm having a little trouble following this part. I understand the retrieval portion but I don't quite follow how you mean the container to contained will work in terms of table organization. I think I get how this applies to inventory, since I hadn't thought of the problem of remembering what hat, body piece, or jewelry someone is wearing beyond just a list of bag contents. So to take inventory again, each inventory container would then be associated with a person (much like the bank) right?

I wonder how this will affect containers. If I have a backpack, which contains its own items, inside of my backpack do I then need yet another table to keep track of these kinds of containers?

Quote
Direct binary serialization has this kind of problem no matter what other system you're using, so I don't think it's really a pro or con of a database approach here.


I've come to learn that =\ What I meant to say though was that I didn't try keeping it in some textual form, through XML or KV pairs, instead of binary.
06 Mar, 2009, elanthis wrote in the 4th comment:
Votes: 0
Say you have two tables, object_template and object. The object table represents all of the (commonly) mutable properties of an object, such as condition. The object_template table is going to include all the (commonly) non-mutable properties of an object. The object table links to the object_template table.

If you ever find a strong need to edit the properties that are supposed to be non-mutable for just a single object, you can copy the template into a new row, update the template id reference for the object row. You could possibly mark the object template as being a unique template so you know to delete it if the object using it gets deleted.

So far as inventory, I would likely use two tables. One to represent equipped objects and one to represent objects contained by other objects. The minimum they need are just two columns representing the parent (owner) and the child object. You could add more fields to represent when the object was equipped/contained or whatever, but they wouldn't be necessary.

For banks, you're best off with a table that has the bank id, player id, and amount in it, which then links to an individual table defining the banks.
06 Mar, 2009, David Haley wrote in the 5th comment:
Votes: 0
Vassi said:
Unfortunately I'm not sure if the latter would work. For instance, tracking item condition introduces 200 possible values by itself (from -100 to 100). For the former, because certainly not all items will be modified, how would I tell the difference between a template ID and an instance ID? I suppose I could choose a cutoff and say 1-9999 are templates and anything after is an instance, but a small part of me doesn't want to make a cutoff if I can avoid it.

I suppose that you might need to keep track of instances after all if your items will be this different (which I guess makes sense for exactly things like condition).

You can make IDs be a pair, type/id, such that the type specifics template vs. instance. Just what comes to mind off the top of my head. I agree that cutoffs sound like a Bad Idea.
Or, you can make all IDs be of the same type, and add a field to the object table which specifies if it's a template or an instance. I guess it depends on whether you have more templates/instances than references to said, if you're concerned about space usage.

Vassi said:
I will admit I'm having a little trouble following this part. I understand the retrieval portion but I don't quite follow how you mean the container to contained will work in terms of table organization. I think I get how this applies to inventory, since I hadn't thought of the problem of remembering what hat, body piece, or jewelry someone is wearing beyond just a list of bag contents. So to take inventory again, each inventory container would then be associated with a person (much like the bank) right?

I was thinking of just having a single relation that maps container ID to contained ID, where container ID is a reference to a thing (room, player, an object, …) and the contained ID is a reference to an object. You might need a pair-based ID for the container since it can be one of many things.
Then an inventory could be represented as either the player itself, or some special other thing that is otherwise attached to the player.

Vassi said:
I wonder how this will affect containers. If I have a backpack, which contains its own items, inside of my backpack do I then need yet another table to keep track of these kinds of containers?

I wouldn't. I would just make the container ID refer to the object, and then you still have a single mapping from container id to contained id.

EDIT: just saw Elanthis's response. So the above didn't take it into account…
06 Mar, 2009, Vassi wrote in the 6th comment:
Votes: 0
Ok, I think this is a good place to start. My biggest remaining concern, I suppose, is making sure I don't get stupid with the queries. It looks like I'll have to make at least three separate pulls just for player loading, I'll have to be careful to group together as many queries as possible in every connection attempt.

I'll try to turn this advice into a working schema and then I'll probably run it by you guys again.
06 Mar, 2009, quixadhal wrote in the 7th comment:
Votes: 0
To give a short, quick reply… Use a prototype table for the default data (and the ones you will have edited via OLC), and an instance table for actual in-game objects. Then use junction tables for containment.

For example:

proto_objs:
obj_id integer primary key not null,
name text not null,
… more fields …

inst_objs:
obj_id integer primary key not null references proto_objs (obj_id),
inst_obj_id serial not null,
name text not null,
… more fields …

The idea is that you want your instance table to always refer to valid objects that are in the prototype table, which lets you implement cascade deletes if you so desire (remove a prototype and all instances also disappear). More importantly, you then never have to worry about an instance being orphaned by not having a prototype to refer back to. If you want "virtual" objects, just make a dummy prototype that's a "generic object".

A serial is shorthand for an integer whose value is auto-incremented and linked against a sequence. These are unique and monotonically increasing, so you will never get collisions (unless you exceed the data range – 2 billion?). You don't HAVE to include fields from the prototype which can't be changed (name?), but then you have to use views to access all the data, which is maybe more of a pain.

Once you have that setup, you could implement player inventory as a junction table between the player table and the object instance table.

player_inventory:
player_id integer not null references players (player_id),
inst_obj_id integer not null references inst_objs (inst_obj_id)

So, select * from player_inventory where player_id = 4, would give you all instance id's in player 4's inventory.

Sorry, I wrote a bit hastily on my way out the door, but feel free to ask if something makes no sense. :)
Random Picks
0.0/7