[MUD-Dev] [DGN] database design

[Steven King]

Mike wrote:
> 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.

Absolutely, especially if you are expecting your tables to be large.
5000 possible players with 100 possible skills and 300 possible
items racks up pretty quickly.  The initial design would need to be
laid-out well and if you forsee any expansion, it should be
considered as well.

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

This is one of the reasons for a well-planned design.  You can
over-normalize and make it very difficult to pull the data you need:
jumping through hoops just to find a simple stat (ex:
player->playerinventory->item->materialresource[worth]) when you
could denormalize a bit and store the material's worth with the
item.  That's probably a bad example, but saves you a step
nonetheless.  5000 players * max 200 items each * 300 item types *
30 material types = maximum 9,000,000,000 rows to search through
just to find out how much an ounce of the iron your sword is made
from is worth (vs. 300,000,000 by denormalizing one step).  Worst
case scenario, of course, and indexes help tremendously.

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