Considerations about tmwweb and upcoming database backend

Content and general development discussion, including maps, quests, and server code from the development team.
Post Reply
Exceptionfault
Peon
Peon
Posts: 21
Joined: Sun Jul 20, 2008 4:46 pm
Location: Germany
Contact:

Considerations about tmwweb and upcoming database backend

Post by Exceptionfault » Mon Jul 21, 2008 1:14 pm

Hi all,

here are some considerations about backend of tmwweb and the current database
structure. Maybe this is the wrong place for that, but atm I`am not able to post via
dev mailinglist.

1.) SQL scripts for installation, updates, patches etc. should not be
compiled into servercode (e.g. datastoragesql.hpp). This makes
setting up new servers easier (maybe for rolling upgrades).

2.) Suggestions on table "tmw_accounts"

a.) Create unique index on email to prevent multiple accounts with
same mailadress.

b.) Add column with registration date (not null) and date of last
login (nullable). So you can easly identify dead accounts if
regdate - current date is "high" and logindate is null, the user
registers, but never logs in.
If last login > 3 months you can ping the user via mail or kick
its account.

c.) Add column REGISTRATION_KEY VARCHAR(32) NULL. After registration
we should validate it`s mailadress and send a registration_key
to the given mailadress. The key should be saved in the column
until the user validates its address. If reg_key is filled and
registration date (b.) is long time ago, the user never
validates its mailaddress and can be kicked.

3.) Suggestions on table "tmw_characters" ordered by priority (imo)

a.) Relocate all columns for experiences and skills to other tables.
Currently this is not a relational datamodell and not really
open for extensions. What if you decide to add a lot more
experiences like woodcutting, tailoring etc.

I suggest adding 3 new tables for skills.

"tmw_skills" holds a list with all available skills. The
description can also be used for generation of player docs.
Maybe there should be some more columns to define restrictions
on some skills? (something like character classes?)

Code: Select all

CREATE TABLE tmw_skills (
           skill_id      INTEGER PRIMARY KEY NOT NULL,
           skill_group   INTEGER NOT NULL,
           description   TEXT
        );

This is for informational usage. Skills can be grouped by this
way.

Code: Select all

CREATE TABLE tmw_skill_groups (
           skill_group_id   INTEGER PRIMARY KEY NOT NULL,
           description      TEXT
        );

Here store the value for each char and each skill.

Code: Select all

CREATE TABLE tmw_char_skills (
           char_id       INTEGER REFERENCES tmw_characters,
           skill_id      INTEGER REFERENCES tmv_skills,
           char_value    INTEGER DEFAULT 0 NOT NULL,
           --
           PRIMARY KEY ( char_id, skill_id )
        );

b.) Add a column for storing character type.
The char is a "normal" player, or its a gm or even more
administrative, maybe a ghost invisible to all and with full
administrative rights for debugging purposes. So you are all
allowed to have chars for administration purposes and for
normal playing if the time allows ;)

c.) Split column "money" in more columns, or relocate to another
table in case of splitting currency in tmw into more currency
units (e.g. gold, silber, pence or similar) Maybe good for
economy and usability (prices like 10000000 are ugly on screen)

4.) Suggestions on table "tmw_guilds" and "tmw_guild_members"

a.) Table "tmw_guilds" lacks some useful informations like
shortsign of a guild (max 10 chars), a description, maybe a
url to the homepage. Also informative: who founds this guild and
when? Has this guild a hq and where (x, y, map_id) is it?

b.) Adding fields about qulification per user in a guild? Which user
may approve new members, which user may drop memebers?
=> administrative rights?!

x.) Btw. i`ve read, that someone plans to implement a worldwide
chat for all online players? Don`t forget the guild chat ;)

5.) I`am not sure what the current code in tmwweb really is for, but I tend
to redesign most of it using almost the same coding conventions like
here http://wiki.themanaworld.org/index.php/ ... guidelines
and completing the wiki with extended guidelines for php.

At first one should concentrate on coding backend objects, maybe realizing
same database factory classes like used in tmwserv/dal. Afterwards one
should implement a clean object layer (hopefully using php5) corresponding
to the defined database model.

When backend and object layers are done, working on frontend and
integration into the homepage should be easy doing. Consider using a
template engine like smarty (http://www.smarty.net/) so the designers can
do it`s job.

greetings

Exceptionfault
Never say: "Always"! Always say: "Never say never"! - Tom Kyte @ Ask Tom Live in Berlin 2008

Image
User avatar
ElvenProgrammer
TMW Adviser
TMW Adviser
Posts: 2526
Joined: Tue Apr 13, 2004 8:11 pm
Location: Italy
Contact:

Re: Considerations about tmwweb and upcoming database backend

Post by ElvenProgrammer » Mon Jul 21, 2008 1:40 pm

Exceptionfault wrote:
5.) I`am not sure what the current code in tmwweb really is for, but I tend
to redesign most of it using almost the same coding conventions like
here http://wiki.themanaworld.org/index.php/ ... guidelines
and completing the wiki with extended guidelines for php.
The current trunk is not meant for reference anymore. We started a rewrite branch against which you should start coding. There's not much in there but that what comes for a complete rewrite.
Post Reply