[MUD-Dev] database design

[Sean Kelly]

On Wed, 18 Aug 2004, Lazarus wrote:

> I am curious how others have choosen to structure your player data
> base with respect to variable count attributes.  In particular, I
> am looking at how one would take a standard mud text player file
> and store that information in a sql data base.  Most of the single
> occurance fields like player name, level, gold, last logon time
> ... that sort of thing maps fairly well onto a row in a table.  It
> is just those things that you can have a variable number of
> entries, like inventory and skill settings, that I start to
> ponder.

The typical implementation would probably be a table containing a
player id, an item id, and perhaps a stack size.  An alternate for
fixed-size inventories might be a long string of columns in a player
or inventory table.

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

Less than a million rows is really nothing in terms of table size,
so I wouldn't worry.  Just make sure that the DB schema and the way
DB operations are performed is tuned correctly.  Careful use of
indexes and pre-compiled operations like views and stored procedures
can have a tremendous impact on performance.  Plus you may want to
consider memory caching, worker threads, etc.

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

So you're saying that some operation can potentially affect every
item in a player's inventory?  Seems implementation-specific, but
you may be able to fix things with a single query or it may be a
better idea to add some sort of "effect" table linked to the player
where item information is mutated when it's queried rather than
manipulating the DB entries themselved.

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

I'm not quite sure what you're asking.  When would an application
need to update all of a player's skills at the same time?

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