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

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.

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.

• +1. This is the only answer that uses a relational database as a relational database. Anything other than this or the other extreme of Nevermind's blob suggestion and just using the DB as a data store, are horrible plans.
– user744
Nov 22 '10 at 10:56
• +1 But these approaches seem kind inflexible. I apologize if I sound inconsistent, but I want to be able to create the database structures for items essentially once... ...and then code more features for items, make more inserts into the database for items, but not have to change the database again in the future (with infrequent exceptions, of course). Nov 23 '10 at 0:16
• If you want the relational database to know about your fields and optimize the representation of them, you need to add them somewhere. It's like static types. If you want your compiler to know about your fields and optimize the representation of them, you need to declare types. The alternative is a dynamically typed approach, used by some of the document databases, or by encoding your data into the relational database in some way. The database won't know what the fields are or be able to optimize their storage, but you'll gain some flexibility. Nov 24 '10 at 17:41

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

• I don't really see a lot of flexibility coming out of that system. For example, if I wanted a glass vial, able to be used as a weapon, and to be 'sharp'... ...I would be out of luck. Plus, for every new type of item, I would have to actually create database structure for it. Even if there were only going to be one or two of the type. Nov 8 '10 at 1:45
• The points you bring up are very valid, and highlight additional examples of why relational databases are not a good fit for this specific situation. The model I presented merely shows the best way of laying out the data in such a manner that memory is conserved and SQL functionality is retained. In order to store items that are of multiple types, the best way to represent that data under this model would be to create a potion_weapon table with the appropriate potion and weapon properties. Again, this solution is NOT ideal, but it's the most elegant solution when using a relational database. Nov 8 '10 at 3:56
• @Ari: No disrespect intended, but his problem is precisely why the relational approach is more valid, not less. It's well-known that NoSQL (or NoRel) databases are great for high-volume reads, distributed/highly-available data, or poorly-defined schemas. This case is simply a classic many-to-many association, and dbs like Mongo don't do this as well as an RDBMS. See stackoverflow.com/questions/3332093/… Nov 23 '10 at 13:36
• @alphadogg How is this a many-to-many relationship? In the database, weapons know about weapon types, but weapon types have no need to know about the weapons they are associated with. If for some reason you want to know all the weapons that are of a specific weapon type, you would simply write a query that iterates over the weapon document collection. Nov 24 '10 at 16:16
• @Ari: In the OP's question one weapon may have many types, and one type may be linked to many weapons. For example, a potion and a sword are holdable. A sword is both holdable and weapon. Nov 25 '10 at 17:23

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.

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.

• Since (from my understanding) the game is multiplayer and supports PvP, there is a likelihood that most gameplay information is not stored on the client. If that is accurate, then every time data is read from the table, it will will have to be deserialized before being remotely useful. As a result, this format makes querying items by their properties VERY expensive; for example: if you want to retrieve all the items that are of type "weapon", you'd have to retrieve every item, deserialize each one, then manually filter for for the "weapon" type, as opposed to running a simple query. Nov 7 '10 at 17:20
• From my experience, all items are read into in-memory cache at the start of the game anyway. So you only deserialize them once at startup, and do not use the database at all afterwards. If this is not so in this case, then I agree, storing serialized objects is a bad idea. Nov 7 '10 at 17:52
• Hmmm, the only thing is that this leaves me with an interface -required- in order to edit the data, which would be inconvenient. Nov 21 '10 at 4:05

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.

• I like the idea, 'cause it allows an item to hold it's own traits, and I don't know that I'm going to need to search the database for the traits, but I'm not really sure how to make that work in a relational context. I guess that each bit would map to the incremental ids of the traits in the database... Nov 22 '10 at 18:24
• I guess bitmasking is more useful for a fixed number of traits, though? Nov 22 '10 at 19:03
• Very much so. What happens if you've used up your last "bit"? How easy is it to increase the data type to gain bit and percolating that through your app layer, or to add a column and do the same? Also, is your particular database engine well-tuned for indexing for bit operations? It's best in a relational database to stay atomic with data columns. Don't lump multiple domains into one column. You can't tap into the platform's benefits. Nov 22 '10 at 19:40
• If you wanted a lookup table in the DB for the traits (probably a good idea as you can then add them dynamically to any editor then) then it would just have something like this: id,bitmask,description. 1,1,"Holdable"; 2,2,"Weapon"; 3,4,"Breakable", etc. With regards to the fixed number, yes this is true. But if you use a 64bit int you should have plenty of scope. Nov 25 '10 at 12:03

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.

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.)

• If you are going to refer to them collectively, don't even bother talking about databases. NoSQL is not an entity that can be discussed meaningfully, it is a useless mass reference that SQL fanbois use in order to apply the flawed logic that if one database of this defined group lack a feature then all of them lack that feature. Nov 22 '10 at 19:56
• NoSQL is the term that NoSQL pushers chose to adapt. I don't know why, it's not very descriptive. But it's not some kind of pejorative. Having worked with several, I think alphadogg's description of them is fair.
– user744
Nov 22 '10 at 20:01
• Maybe my comment was a bit harsh, still, I hate that name and grouping, it's impossible to have a qualified debate about the finer details of different database systems when people keep this simplified world view. Nov 22 '10 at 20:17
• @eBusiness: I find your comment amusing, given that it is the anti-RDBMS camp, if there is one, that self-anointed their group of technologies as "NoSQL". Admittedly, many of them would like to see that name deprecated, in hindsight. Especially as many of them are finally layering SQL over their physical implementations. As for talking about them, I didn't take your comment as harsh, or I have thick skin, your choice! :) I mean, one can talk about the Tea Party, why not the NoSQL group of databases? Nov 22 '10 at 20:42
• That's fair, I recognize that the relationship is totally many to many. The thing that's stopping me from creating it in truly normalized style is the thought that the "stuff" that would make up an item is then going to be spread across at least two tables. I don't like the idea of being unable to create atomic inserts, I think. Nov 22 '10 at 22:36

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 ^^

• Well, I will create the code and classes and all that in code only first... ...just gotta translate that into the existing database structure after. Nov 22 '10 at 19:05

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.

• What happens when you introduce a new trait? How often could this happen? What would be the downstream effects on your code maintenance workload? Contrast with storing it as a many-to-many whereby your object instances are dynamically built up (via your choice of ORM) to hold that data. Nov 22 '10 at 20:46
• Thanks, that's a good point, adding new traits isn't going to be trivial. You've added to my analysis paralysis. :p Nov 22 '10 at 22:42
• Sorry, but this is pretty critical. You just need to think it through. Model the exercise in your mind of what you'd have to do when some weapons need to be updated to add a new trait so that your game enhancement can work. Nov 25 '10 at 17:27

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.

• This is terrible design, never do it. You'd be better off just not using a database. Nov 23 '10 at 1:16

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.

• Err, I've already got a database in use & integrated with the rest of the site. Nov 22 '10 at 16:17