I currently struggle with creating the database structure for my RPG. I got so far:


  • area (id)
  • monster (id, area.id, monster.id, hp, attack, defense, name)
  • item (id, some other values)
  • loot (id = monster.id, item = item.id, chance)
  • spawn (id = area.id, monster = monster.id, count)

It is a browser-based game like e.g. Castle Age. The player can move from area to area. If a player enters an area the system spawns, based on the area.id and using the spawn table data, new monsters into the monster table. If a player kills a monster, the system picks the monster.id looks up the items via the the loot table and adds those items to the player's inventory.

First, is this smart? Second, I need some kind of "monster_instance"-table and "area_instance"-table, since each player enters his very own "area" and does damage to his very own "monsters". Another approach would be adding the / a player.id to the monster table, so each monster spawned, has it's own "player", but I still need to assign them to an area, and I think this would overload the monster table if I put in the player.id and the area.id into the monster table.

What are your thoughts?

Temporary Solution

monster (id, attackDamage, defense, hp, exp, etc.)

monster_instance (id, player.id, area_instance.id, hp, attackDamage, defense, monster.id, etc.)

area (id, name, area.id access, restriction)

area_instance (id, area.id, last_visited)

spawn (id, area.id, monster.id)

loot (id, monster.id, chance, amount, ?area.id?)

An example system-flow would be: Player enters area 1:

  • system creates area_instance of type area.id = 1 and sets player.location to area.id = 1

If Player wants to battle monsters in the current area:

  • system fetches all spawn entries matching area.id == player.location and creates a new monster_instance for each spawn by fetching the according monster-base data from table monster. If a monster is fetched more than once it may be cached.

If Player actually attacks a monster:

  • system updates the according monster_instance, if monster dies the instance if removed after creating the loot

If Player leaves the area:

  • area_instance.last_visited is set to NOW(), if player doesn't return to data area within a certain amount of time area_instance including all its monster_instances are deleted.
  • 1
    \$\begingroup\$ I don't think there's any need to create an area instance, as each area won't have specific value changes (and there will only be one instance). I would just replace area_instance.id with area.id. \$\endgroup\$ Commented Jun 20, 2011 at 11:09

2 Answers 2


The problem will be dealing with instances of monsters, especially as you can have many monsters of the same type. The easiest way IMO would be to have a special table that deals with instances, and have a limited number of instances per area, say 10.

This doesn't stick with what is considered 'good' SQL but it should work providing you can limit the instances :

Table AreaMonsters { player.id, area.id, monster1.id, monster1.currHP, monster2.id, etc... }

This will allow you to insert a new line with all the data for an area, and it will be persistent throughout the game. The only problem really is the limit to instances.

* Original Answer *

From looking at your tables, the setup seems ok, but beware of using reserved words (like count).

I'm not too familiar with Caste Age, but why cant you just keep the instances client-side? This shouldn't pose a problem if each player has their 'own' area instance.

A problem would be the persistance of the data during the game, can the player only be in one area at once? Will the damage he dealt be forgotten once he leaves?

Another question would be whether each monster keeps his current health. If this is not the case it could be possible to just keep track of the number of each monster in the area instance, and assume that when the player starts attacking, the monster has full health.

  • \$\begingroup\$ The names are just coined on the fly. I use others than I wrote here :) no worry. I just wanted to represent the stuff so everyone understands what I mean. \$\endgroup\$ Commented Jun 19, 2011 at 20:07
  • \$\begingroup\$ @daemonfire300 Is it possible for you to store local variables then or nay? \$\endgroup\$ Commented Jun 19, 2011 at 20:09
  • \$\begingroup\$ What do you mean with local variables? I want to have a general monster table keeping the monster values. Like a sheet where the application can look up what damge monster "Dragon" deals. But I changed the purpose of the monster table to "monster_instance". Monster now holds many monster_instances, identifable by area.id and player.id So the monster stays in a certain area even if the player leaves. If the player comes back the monster still has the same values as the player left. \$\endgroup\$ Commented Jun 19, 2011 at 20:21
  • \$\begingroup\$ @daemonfire300 : Yeah I've been playing a bit to try and see what you need, do you want to make a game exactly like this one? \$\endgroup\$ Commented Jun 19, 2011 at 20:35
  • \$\begingroup\$ @3nixios : no I just took it as example. \$\endgroup\$ Commented Jun 19, 2011 at 20:37

Your suggestion and 3nixios's answers are not properly normalized. Here is a table schema that might do the trick:

table area
    id, name
    // might be better kept in a separate table restriction and linked via the table areaRestrictions
    access restrictions

table monster
    id, name, exp, attack, defense, maxHp

table areaMonsters
    count // or some other spawn related data

table monsterItems

table areaInstance
    lastVisit // timestamp, default NOW()

table monsterInstance
    hp // plus other instance instead of type related data

Character (charID) enters an area (aID)

  • Check if there is an entry in areaInstance with characterID=charID and areaID=aID (and lastVisit sufficiently high in case you don't want to delete obsolete records)
    • If a record exists, use it and set lastVisit to NOW()
    • If not:
      • Create a new record in areaInstance
      • Fetch all the entries in areaMonsters
      • Create new records in monsterInstance according to the entries in areaMonsters

Character looks for enemies in an area

  • Let the character enter the area, if that has not been done before (see above)
  • Fetch all entries in monsterInstance with the relevant areaInstanceID
  • Let the player choose a target / select a target by some algorithm

Player fights a monster

  • Reduce hp in the relevant monsterInstance record.
  • If the monster dies:
    • Fetch all entries in monsterItems to determine loot
    • Delete the monsterInstance record

Player does not visit an area for some time

  • Have a background process check periodically if there are any areaInstance records with lastVisit too long ago. Delete these records.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .