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

tables:

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