[MUD-Dev] database design

[Hans-Henrik Staerfeldt]

On Thursday 19 August 2004 03:13, 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?

Yes. If you have a 64 bit file system, MyISAM tables can handle lots
and lots. I have a table of 167 million rows of 12.56 GB and a 3.28
GB index.  (not for a MUD but 3D atom data, but it demonstrates
things nicely)... and that was MyISAM not ISAM.. convert your tables
- then get the latest MySQL :-)

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

Don't do that. Just update what changes... Let all item instances
have a unique item identifier, and a parent identifier for the item
heirarchy.  Having a nice objecet oriented way of manipulating your
object data will ensure that you only update the objects you need
to. Design your rows with fixed lengths of possible - this helps
alot on updates (then the key index probably dont need updating).

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

You could do that to limit disk access. Its risky, but thats a
priority question. One way could be to both set the dirty bit and
add the object you need to store to an event list, so it will be
scheduled to be saved at a later time (in a few seconds). If you
modify an object that is 'dirty', you just do so. When an update
event is due, you flush the object to the database, and clear the
dirty bit. This way if objects are changed in a number of ways over
a short time, they will only be saved once or twice...

> I suspect that data base design issues like this won't have a lot
> of impact on the type of game I develop (5000 total player files,
> max 50 concurrent players), but would be interested in hearing
> some of the techniques that people who develop MMOG's employ.

- for starters, I would suggest you just try out flushing everything
to the database directly without any delay, and see how it works for
you....

Since youre working with MySQL, i would suggest trying out
DBDesingner4.

--
--Hans-Henrik Stærfeldt
_______________________________________________
MUD-Dev mailing list
MUD-Dev at kanga.nu
https://www.kanga.nu/lists/listinfo/mud-dev