[MUD-Dev] [DGN] database design

[szii at sziisoft.com]

Steven King wrote:
> Mike wrote:

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

That scenario wouldn't be correctly normallized, for reference then.
Your resources table is the only table which should have value.

The fact that "player x" has that item in his nth-deep nested
backpack doesn't matter.  If you have the ItemID then you have all
you need.  "Conditional world information" (ie, gold is worth more
when held by a prospector who can assertain purity and thus
negotiate a higher-value) is most of an application-logic issue than
a data store issue.  Context, as well, is game-state and not a
data-store problem.

Thus, per your example, you would have to search an indexed table of
300 items to pull the "worth."

You could come up with some extremely gnarly scenarios if you really
try, but so long as you keep the boundary between your application
logic and data store well defined then all of your database stuff
should be pretty easy.  You can most easily blur the lines when you
use stored procedures though since it's so tempting to abuse them.

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