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.
