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