How to model multiple "uses" (e.g. weapon) for usable-inventory/object/items (e.g. katana) within a relational database

Question

So I'm working on expanding the uses of items over at www.ninjawars.net, and I'm not exactly sure how to represent them flexibly in the relational database that we use.

I may be barking up the wrong tree, so feel free to make suggestions in other directions, but currently I'm thinking that each item should have relational "tags".

For example, a Katana is currently a row in the "items" database. To make it into a weapon, and a holdable thing, I was thinking that I would have a database of "traits", and a item_traits table that simply linked between them.

// Objects and their basic data

item_id | item
1 | Naginata


// Things that objects can do

trait_id | trait
1 | weapon
2 | holdable

// How those objects do those things, e.g. powerfully, weakly, while on fire

_item_id | _trait_id | item_trait_data
1 | 1 | damage: 5, damage_type: sharp, whatever, etc

I'm not really sure how to model the extra data that results (e.g. the damage that a sword will do, the damage_type, etc).

I'm also not especially happy that the whole of an item would be stored in more than one place, e.g. in order to create a copy of an item with a different name, like a "short sword", I would have to copy from multiple tables to create the duplicate item.

Is there a better way to lay this stuff out that I'm missing?

Edit: I should just note that I've already got a postgresql database in use on the site, which is why I want to use it for my data storage.

Edit: I've added an answer for the implementation that I'm currently looking at.

Answer 1

I'm going to mildly disagree with everyone and say that the relational approach is reasonable here. What's interesting here is that items can have multiple roles. The main issue will be that the mapping between this relational layout and an OO layout in the code won't feel “natural”, but I think on the database side multiple roles can be expressed cleanly (without weird encodings or redundancy, just joins).

The first thing to decide is how much of the data is item specific and how much is shared by all items of a given type.

Here's what I'd do if all data is item specific:

// ITEMS table: attributes common to all items
item_id | name        | owner         | location             | sprite_id | ...
1       | Light Saber | 14 (Tchalvek) | 381 (Tchalvek house) | 5663      | ...

// WEAPONS table: attributes for items that are weapons
item_id | damage | damage_type | durability | ...
1       | 5      | sharp       | 13         | ...

// LIGHTING table: attributes for items that serve as lights
item_id | radius   | brightness | duration | ...
1       | 3 meters | 50         | 8 hours  | ...

In this design, every item is in the Items table, along with attributes that all (or most) items have. Each additional role that an item can play is a separate table.

If you want to use it as a weapon, you'd look it up in the Weapons table. If it's there, then it's usable as a weapon. If it's not there, then it can't be used as a weapon. The existence of the record tells you whether it's a weapon. And if it's there, all its weapon-specific attributes are stored there. Since those attributes are stored directly instead of in some encoded form, you'll be able to perform queries/filters with them. (For example, for your game's metrics page you might want to aggregate players by weapon damage type, and you'd be able to do that with some joins and a group-by damage_type.)

An item can have multiple roles, and exist in more than one role-specific table (in this example, both weapon and lighting).

If it's just a boolean like "is this holdable", I'd put it into the Items table. It may be worth caching "is this a weapon" etc. in there so that you don't have to perform a lookup on the Weapons and other role tables. However, it adds redundancy so you have to be careful to keep it in sync.

Ari's recommendation of having an additional table per type can also be used with this approach if some data won't vary per item. For example, if the weapon damage doesn't vary per item, but the roles still vary per item, you can factor shared weapon attributes out into a table:

// WEAPONS table: attributes for items that are weapons
item_id | durability | weapon_type
1       | 13         | light_saber

// WEAPONTYPES table: attributes for classes of weapons
weapon_type_id | damage | damage_type
light_saber    | 5      | energy

Another approach would be if the roles played by items do not vary by item, but only by item type. In that case you'd put the item_type into the Items table, and can store the properties like "is it a weapon" and "is it holdable" and "is it a light" in an ItemTypes table. In this example I also make item names not vary per item:

// ITEMS table: attributes per item
item_id | item_type    | owner         | location
1       | light_saber  | 14 (Tchalvek) | 381 (Tchalvek house)

// ITEMTYPES table: attributes shared by all items of a type
item_type   | name        | sprite_id | is_holdable | is_weapon | is_light
light_saber | Light Saber | 5663      | true        | true      | true

// WEAPONTYPES table: attributes for item types that are also weapons
item_type   | damage | damage_type
light_saber | 5      | energy

It's likely that itemtypes and weapontypes don't change during the game, so you can just load those tables into memory once, and look up those attributes in a hash table instead of with a database join.

Answer 2

Unfortunately, situations like this are where relational databases (such as SQL) fall short, and non-relational databases (such as MongoDB) excel. That being said, it is not impossible to model the data in a relational database, and since it seems like your codebase is already reliant on SQL, here is the model I would go with:

Instead of creating an item and uses table, create a table and "[item]_type" reference table for each item type.

Here are a few examples:

weapon_type_id | weapon_type
1 | sharp

weapon_id | weapon| weapon_type_id | damage
1 | Short Sword | 1 | 5

potion_type_id | potion_type
1 | HP
2 | Mana

potion_id | potion| potion_type_id | modifier
1 | Healing Elixer | 1| 5
2 | Mana Drainer | 2| -5

This solution gives you a lot of long-term flexibility and scalability, it reduces (if not eliminates) wasted space, and is self-documenting (unlike "item_use_data"). It involves a little more setup on the developer's end, but, in my opinion, it is the most elegant solution available for situations where you need to use a relational database to store your data. Generally speaking, non-relational databases are much better for game development, as they are more flexible in how they model data, while being more performant than SQL-based databases - making them a "win-win" choice.

Edit 1: Corrected an error with the potion_type_id field

Edit 2: Added more detail on non-relational vs relational databases to provide additional perspective

Answer 3

First off, dump the object-oriented inheritance approach and go with a component-based system.

Once you've done that, the SQL layout suddenly gets far easier. You have one table for each component type with a shared ID number. If you want item #17, you go look up "item ID 17" in every table. Any table that has a key gets its component added on.

Your Item table contains all the required data for items in general (name, sell price, weight, size, anything else that's shared among all items.) Your Weapon table contains all the appropriate data for weapons, your Potion table contains all the appropriate data for potions, your Armor table contains all the appropriate data for armor. Want a breastplate, that's an entry in Item and an entry in Armor. Want a swordhelm that you can drink, you just put an entry in each table, and bam, you're done.

Keep in mind that this same pattern isn't particularly item-specific - you can use this for creatures, zones, anything else you may want. It's surprisingly versatile.

Answer 4

Using SQL was your grave mistake. It absolutely NOT suited for storing static game-design data.

If you can't move away from SQL, I'd consider storing items in a serialized from. I.e.

item_id (int) | item (BLOB)
1             | <binary data>

Of course, that's ugly and throws all the SQL "niceties" out of the window, but do you actually need them? Most probably, you read all of your item data at the game start anyway, and never SELECT by anything other than item_id.

Answer 5

Depending on how many traits you're likely to need you could use a simple bitmask for objects with the different bits corresponding to different traits:

000001 = holdable
000010 = weapon
000100 = breakable
001000 = throwable
010000 = solids container
100000 = liquids container

Then you can do simple bit tests to see if an object can be used for a certain function.

So "glass bottle" might have a value of 101111 meaning it's holdable, can be used as a weapon, it breaks easily, you can throw it and it can contain liquids.

Any editor you create for items can then have a simple set of check boxes to enable/disable traits on an object.

Answer 6

On our project we have item_attributes for the different "extra data" that an item may have. It's laid out something like this:

item_id | attribute_id    | attribute_value | order 
1        25 (attackspeed)       50             3    

Then we have an attributes table that looks like so:

id |   name       | description
1    attackspeed    AttackSpeed:

Ari Patrick is right though, ultimately relational db's aren't made for this. The downside is that we have some pretty complex procedures to generate new items (done through an external tool - which I highly recommend, Do not try and manually add these, you'll only confuse yourself)

The other option you have is using a scripting language to create the item templates, then you can easily parse those in and use that to create new items. You still of course have to save the item data in the database but at that point you don't need to worry about specifics of creating new items, you can pretty much just copy and old script file, change some information and you're good to go.

Honestly, if we were to re-do how we create new static items we would probably go for a much simpler approach using scripting item templates.

Answer 7

This is your typical many-to-many relationship, nothing too esoteric for any capable relational database. You have many traits for any one objects, and any one trait may be used by one or more different object types. Model three relations (tables), with one being an association relation, and you're done. Proper indexing will assure you of speedy data reads.

Layer an ORM in your code and you should have very few issues going back and forth between DB and middleware. Many ORMs are able to auto-generate the classes themselves too, and so become even more "invisible".

As for the NoSQL databases, there's no reason you can't do it with those. It's currently fashionable to cheerlead for that technology in trade rags and blogs, but they come with a slew of its own issues: relatively immature platforms, great for simple infrequently-changed reads (like a one-to-many twits in a profile) but poor for complex dynamic reads or updates, poor supporting toolchain, redundant data and the accompanying integrity issues, etc. However, they offer the appeal of better scalability because they eschew to varying degree transactional capabilities and its overhead, and easier models of distribution/replication.

The usual hobgoblin of relational databases vs NoSQL databases is performance. Different RDMBSes have different degrees of overhead involved that make them less preferred for scaling to the levels of Facebook or Twitter. However, it is very unlikely you'd face those issues. Even then, simple SSD-based server systems can make the performance debate useless.

Let's be clear: most NoSQL databases are fundamentally distributed hash tables, and will limit you the same way a hash table in your code would, ie. not all data fits well into that model. The Relational Model is much more powerful for modeling relations between data. (The confounding factor is that most RDBMSes are legacy systems that are poorly tuned for the demands of the popular 0.0001% of the web, namely Facebook et al.)

Answer 8

This is where I find more modern OR mappers to be really useful, like Entity Framework 4 and its (CTP) code-first feature. You just write the classes and their relationsships in regular code and without even having to decorate them or manually run any tools, EF will generate the backing store in SQL format for you, with required link tables and all... it really unleashes creativity imo ^^

Answer 9

Here's what I'm now considering:

Since every "trait" essentially requires changes in the code anyway, so I've decided to just keep the traits (and any default data they require) in the code itself (at least for now).

E.g. $traits = array('holdable'=>1, 'weapon'=>1, 'sword'=>array('min_dam'=>1, 'max_dam'=>500));

Then items get a "trait_data" field in the database that will use the json_encode() function to get stored in the JSON format.

item_id | item_name | item_identity | traits
1 | Katana | katana | "{"holdable":1,"weapon":1,"sword":{"min_dam":1,"max_dam":1234,"0":{"damage_type":"fire","min_dam":5,"max_dam":50,"type":"thrown","bob":{},"ids":[1,2,3,4,5,6,7]}}}"

Plan is that items will inherit all the default stats from their parent traits, and will only have over-ride traits that specify differences from what the traits set as defaults.

The downside of the traits field is that while I could edit the json part by hand... ...it won't be really easy or safe to do so with data that's in the database.

Answer 10

This is a bit hacky, and I make no guarantees it's good DB design; my DB classes were a while ago, and they're not coming quickly to mind. But if items are guaranteed to have, say, fewer than 10 items, you could give each item an attribute1 field, and attribute2 field, and so on up to attribute10. It would eliminate your need for the many-to-many item-attribute table, at the cost of limiting the number of attributes.

Looking back on that, I'm pretty sure it's terrible design, but it does mean you can stick with a comfortable relational database and not have to go into uncharted territory. It's up to you to decide if the trade-off is worth it.

Answer 11

Nevermind gave a good answer, but I wonder, do you even need a database for this? Storing all the data in a plain file and loading it into the program at launch seems like the smartest way of doing this.

Edit:
Right, in a stateless request driven environment you better keep the data in a database. Write your data in a file and write a piece of code to turn it into a database of the type Nevermind suggested.

Alternately if the number of objects is not too big declaring the lot literally in a code file may be the fastest method.