I think there's some problems with the ownership model of the items...
First, there's a difference between owning an item and posessing an
item. For the sake of simplicity, all posessed items are owned, but not
all owned items are posessed. E.g. a player owns a house and dumps extra
items there for bartering later on. He doesn't hold them in his/her
backpack, but continues to own them.
Also, i don't think the relationship you descibe for ownership is
correct. A player owns/posesses n objects. So the FK should be a field
of the item, not the player. There's 2 ways i can think of to do this:
one table for ownership, one table for posession. Each links an item
record to a player using the PK of each. Or, item record has the PK of
the owning player and a boolean that determines whether its in his/her
posession. Actually, a 3rd way would be the second way, but instead of a
boolean, use the PK of the posessing character.
With the first and third models, you can run into a situation where a
player posesses an object that is owned by another. This could be used
in the implementation of a theft system.
Actually, i think the 3rd model is not in its most normalized form, but
that might not be bad. Otherwise, queries might have to join the
player/character, item and owner/posessor tables and that would
certainly be inefficient.
Also, i've given a lot of thought to modifiers... There's not
necessarily a good way to describe those in terms of a database. You
basically end up with a list of modifications to various RP elements.
These can be attributes (str, int, etc.), derived attributes (health,
mana, attack rating, etc.) or skills (enchanting, long sword, etc.). Its
too bad i never had a good solution to the problem :) that's okay,
because i don't believe the character sheet, attribute/skill stuff is
fully fleshed out yet.
I did however, realize the best solution... Given the complexity of role
playing games and the various concepts within them, the best way to
express these relationships is through an RP description language.
Considering that UML is a way to represent design concepts, why can't
somebody develop a similar language to represent RP concepts. See...
Optimal solution ;)
> -----Original Message-----
> From: arianne-general-admin@...
> [mailto:arianne-general-admin@...] On
> Behalf Of Keith C.
> Sent: Thursday, August 29, 2002 10:44 AM
> To: arianne-general@...
> Subject: Re: [Arianne-general] Re: Spec for Player Management
> Good day all,
> After digging through my Arianne archive, I found the
> character info I was looking for.
> Without getting caught up in the sql, let me list the data
> that I think is relevant to a user/character combo by table.
> Foreign keys are marked (fk) and primary keys are marked (pk).
> username (pk), full name, password, login failures, created
> datetime, last accessed datetime, last attempted login,
> secret question and answer (for retriving lost passwords),
> email address, country/timezone(?), primary language (fk),
> arianne client version
> - allows for semi-customized emails with full name,
> country/timezones, and primary language.
> After all, it doesn't help to know that the server is going
> to be down from 4-6am if you don't know what timezone the
> server is in.
> - this will also allow a user to examine what the
> predominant language is before they join the game.
> username (fk), character name (pk?), created datetime, last
> accessed datetime, total playing time, various stats
> (strength, agility, dexterity, constitution, maybe more?),
> race(fk), items (fk), gender, current world (fk), class (fk)
> - allows for a single user to have multiple characters.
> - allows race to be stored seperately and can easily add
> more races by adding a table entry instead of changing a constraint.
> - allows for a character to move from one world to another
> if the (in game) method exists.
> - minimizes duplicate data as items are not referenced and copied.
> - may want to have something other than character name as
> a primary key if we want to allow people to have the same name.
> character name (fk - or whatever the primary key is), item
> name (fk), date obtained, number owned
> - allows the admin to monitor this and detect if there are
> more or a particular item than there should be.
> - allows the admin to figure out when a character got an
> item. May help if an exploit is discovered on a particular
> date and items were multiplied.
> name (pk), modifiers* (might require a field for each stat)
> - allows the admin to modify a race as a whole with a
> single edit. This could be fun when the dark elves (drow)
> realize that today is going to be a solar eclipse, so they're
> not weakened as badly. Imagine if there was a regular cycle
> that affected a particular race (lunar cycles on a werewolf?).
> name (pk), abilities
> item name (pk), weight, size, type (weapon, armor, potion,
> etc), cost, modifiers, number allowed, limitations (class and/or race)
> - allows the admin to create/remove objects at will.
> This next portion needs a bit of work and some more thought
> as I have not fully considered the implications of it. Some
> input would be nice.
> world name (pk), admin name, admin email,
> ipaddress/connection info, arianne server version, arianne
> minimum client version, datetime world was first contacted,
> last time world was contacted, successful connection rate
> - this may be incredibly difficult but also fun. I
> already know that in order for this to work properly, the
> character export/import functions would have to determine if
> the target world has the same items and/or races and/or classes.
> I don't believe that this is complete yet, but it gives
> a place to start a figure out the logical flow. It currently
> just takes into account the information concerning the
> character, not anything about the appearance of the character
> (hair, size, etc). If I'm completely wrong or you have
> suggestions/concerns, let me know. I know there's already
> been a bit of work on this, if anyone else is doing it, also
> let me know.
> The sql script below is Miguel's work.
> CREATE TABLE characters (
> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
> name TINYTEXT NOT NULL,
> race ENUM("human","orc","cat","dog") NOT NULL,
> gender ENUM("male","female"),
> rp_strenght INT UNSIGNED NOT NULL,
> rp_agility INT UNSIGNED NOT NULL,
> rp_dexterity INT UNSIGNED NOT NULL,
> rp_constitution INT UNSIGNED NOT NULL
> CREATE TABLE player_character_link (
> id_player INT UNSIGNED NOT NULL,
> id_object INT UNSIGNED NOT NULL,
> PRIMARY KEY (id_player,id_object)
> CREATE TABLE LOGINS (
> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
> login TINYTEXT NOT NULL,
> login_procedure ENUM("password", "RSA") NOT NULL,
> objectid INT UNSIGNED,
> last_datetime DATETIME,
> last_ip TINYTEXT,
> failures SMALLINT UNSIGNED,
> INDEX login_key (login(5))
> Do You Yahoo!?
> Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> Arianne-general mailing list Arianne-general@...