[MUD-Dev] database design

[szii at sziisoft.com]

Lazarus wrote:

> Is storing each item in its own row, indexed by a unique player
> identifier a reasonable solution?  What happens when you have
> 100,000 players with 100-200 items each.  Can an ISAM data base
> handle it?  How bad do 'pfile updates' become when you have to
> update each of the 100-200 items in the ISAM data base every time
> things change with the player inventory.  Likewise with skills.
> Do you store each skill in its own table with rows indexed by a
> unique player identifier?  Do you use dirty bits to tell "this
> skill must be updated" to avoid updating the entire list?

Personally - I would normalize all of it out.

  SkillDefinitions
    SkillID/PK, Skill Name, Skill Description, Effect..etc

  PlayerDefinitions
    PlayerID/PK, Player Name, etc

  PlayerSkillMaps
    PlayerID, SkillID

It'll centralize all of your player skills, and you can VERY easily
add or remove skills from a player, regardless of class-boundaries.
Keep "classes" (if you use them) as prepackaged skill sets.  When
you achieve a "class" the PlayerSkillMaps are updated with the
information in that package.

This does mean that you need application/middleware to manage these
building blocks.

The downside that PlayerSkillMaps with 100k players could be a
couple million rows (not a huge challenge for modern enterprise
RDBMS but still a factor.)  You can mitigate this via multiple
PlayerSkillMaps (PlayerSkillMaps1, PlayerSkillMaps2, etc) and a
column in your player field (PlayerSkillMapTable =
"PlayerSkillMaps1") and via excellent use of indicies.  Since you're
just indexing scalar values the indexes should be pretty fast.

I'm not a game programmer but the challenges aren't much different
than traditional DB performance design so any good book should help
out quite a bit.

On a related note, while I'm a huge fan of this system and have even
written some "generic map handling routines" (which work regardless
of column name/data values) there is one HUGE caveat.

CAVEAT: You CAN over-normallize and make your life a living hell
unless you properly build the application/middleware to manage those
tables.  This means things like hand-editing the database for ad hoc
stuff can get you into deep doo-doo very very quickly, especially if
you use a RDBMS which doesn't support foreign keys.  Trying to keep
your game state consistant using this framework is MORE WORK than
it's worth unless you've built the correct tool sets so that you
execute perfectly every single time.

That said, once those tool sets are in place you can really really
do some neat things (temporary skills, skill-blocking-skills,
time-of-day-based skills, etc)

And, of course, you can use the schema for more than just skills
(items come to mind.)  You update the ItemDefinitions table and
*poof* *poof* *poof* it's global. (Well, you may have to reload the
game-state from the database but you don't have to manage multiple
files/pfiles to walk and find/replace.)

-Mike/Szii
_______________________________________________
MUD-Dev mailing list
MUD-Dev at kanga.nu
https://www.kanga.nu/lists/listinfo/mud-dev