Long Term Database Structure

[AcidicOne]

Okay I seem to have a small confusion with the tutorial db structure. Now the confusion is not how it works, or what is what, but how in setting up a basic framework i of course have updated and filled in things to test with.Then i got to thinking,how is this efficient?
All items,stats,monsters etc are place in separate places,Now i realize from rereading the tutorial over twice it is to remove repeated data to cut down on the overhead of the information stored,and allowing for things to be easily and quickly updated through a admin panel.


This is the tutorial DB for the items in question.

Code: [Select]

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(250) default NULL,
  `password` varchar(50) default NULL,
  `is_admin` tinyint(1) NOT NULL default '0',
  `email` text NOT NULL,
  `confirmed` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

CREATE TABLE IF NOT EXISTS `stats` (
  `id` int(11) NOT NULL auto_increment,
  `display_name` text,
  `short_name` varchar(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL auto_increment,
  `name` text,
  `type` enum('Weapon','Armor','Usable','Foo') default NULL,
  `price` int(11) NOT NULL default '10',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

CREATE TABLE IF NOT EXISTS `user_items` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

CREATE TABLE IF NOT EXISTS `entity_stats` (
  `id` int(11) NOT NULL auto_increment,
  `stat_id` int(11) default NULL,
  `entity_id` int(11) default NULL,
  `value` text,
  `entity_type` enum('User','Monster','Item') default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;

Alright, again not trying to be a pain, or say what the tutorial teaches is in anyway shape or form wrong, but it did give me a nice framework to start with before I started thinking there had to be a better way.Now Onto the question.


Why are my stats kept separate from user? All this does is make me either have a written list of each stat and its ID on paper infront of me when editing( I only say this because there is no admin panel tutorial, so you would be required to edit everything through the back end )or in my case the db structure on a 2nd monitor through phpmyadmin. Now i realize this was done to make for easy quick fixes if need be.This next part I am assuming, so could quite possibly be wrong.

Explore.php

Code: [Select]

$query = sprintf("SELECT name FROM items WHERE id = %s",
mysql_real_escape_string($itemID));
$result = mysql_query($query);
list($itemName) = mysql_fetch_row($result);
$smarty->assign('item',$itemName);
$monster_exp = getMonsterStat('exp',$monsterID);
[b] $smarty->assign('exp',$monster_exp);
$exp_rem = getStat('exp_rem',$userID);
$exp_rem -= $monster_exp;
$level_up = 0;
if($exp_rem <= 0) {
    // level up!
    $exp_rem = 100;
    $level_up = 1;
}
    $smarty->assign('level_up',$level_up);
setStat('exp_rem',$userID,$exp_rem);[/b]
} else {

Now am assuming this deals with xp given to the users,but using this method i would have to hard code each level required and the exp remaining to the next.Would it not be more effective and easier on me as a admin/creator to store each lvl's base attributes(that is if a user gets a stat increase on any given level) in a table, with the values that will change according to lvl,allowing for say, if i raise the lvl max higher, and i wish to change all the users regardless of lvl to coincide with the new lvl ranges, i only have to change one table and not rewrite a entire code section.And On second thought it also updates all users after there first fight after the update in lvl stats has changed,allowing me to create a backup of the old lvl system,which then i can query all users after say 30 days, and if the users stats have not changed it helps me prune inactive accounts.


The  system in mind would work something like this under the condition a user won.(am sure could group the query's etc up, but doing it in steps to show how it would work.) The table for the lvls would have say id 1(aka lvl1) ex_req(exp required to move on to the next lvl)
So in this example, say user was lvl 1, and the xp required to move onto lvl 2 was say 100,it would do a simple query to check if the say 15exp the monster i just killed,granted me over 100, if not then, just present the user with the facts of the battle ex items found,exp earned,money earned etc

Code: [Select]

query db
get item_id
fetch monster exp
add monster_exp to user_exp_total
fetch user_cur_ lvl
check user_exp_total vs user_cur_lvl
if user_exp_total > user_cur_lv
add +1 users_cur_lvl
else
echo "you've gained blah blah exp, gain blah blah gold, gained blah blah item"
end

Would this not make things easier? only having to adjust the base stats for each lvl individually,and adjusting across all users?
and also with this is mind, would this not work as well with the monsters? there for allowing me to change monsters lvls across the board on a whim(minus the exp part of course)

the user_items DB is set up fine i think,although would like to implement a weight limit of sorts, to limit how much a user can carry a given time based on say str stat.

And onto the next question, enity_stats combines user_stats,monster_stats,item_stats in 1 table,now sure this sounded great at first,but again I have to remember all the stats id etc Would it not be more efficient to do the same as the users base stats for lvl above? I am still storing the data needed,and unless I am wrong i would be storing the same amount data in a fewer amount of tables.

So i suppose my real question is for the mysql gurus out there is there any performance gain/loss over reading across multiple tables over reading over just 1 in the long haul, I personally would prefer not to have to redo as much code as possible if the game does succeed, and would rather design my db around the anticipation of say 10000 users or more as oppose to   just 1000 just to find my game has taken off earlier then expected and be forced to either close registration to new users, or close the game and redesign the db yet again.

I have googled the topic and poeple seem to bounce back and forth, am really looking for personal experiences in the matter, which has worked out better for you

**edit**
One final thought before bed,lol
also with in keeping the majority of the user information in 1 table, would this not save a few lines of code here and there? aka saving bandwith/space on the server? Not to mention once finished when released make it easier for newer game admins/programmers making everything easier to find, instead of having to look everywhere.

[Scion]

why are the stats kept seperate....

um dunno why but i can perhapes provide a few hints as to what the seperate stats table allows....

it allows you to have an unlimited number of stats....so if you want to add a stat to see how good a player is at smelling the flowers....easy peasy....just add it ....

the seperate stats and entity_stat tables also allow stats to be linked to users, to monsters, to items, iel basically anything that is an entitiy... if in the future you want to add stats to areas then extend the entity type enumeration to include area and presto the db change is complete.

however if you know for certain that you will only have a certain number of stats then recording them directly in the various tables will cut down on the number of joins you need to include in a db querry....

but remember this that incorrect db indexes are a 1000 times more significant cause of poor db performance than excessive use of joins.

As for storing level requirements and progession in the DB....yes thats what i would do, others might store it in a config file....what you really do not want to do is hard code the actual values into your code.....especially if your still thinking about tweaking them.

oh and as for remembering id's from tables.....you might do that or you can just add the join into the querry and select the stat name.

[codestryke]

First I cannot say because I haven't played with them on mySQL but you could create a View to de-normalize the data for easier retrieval. With Oracle and MSSQL a view is quite fast and highly beneficial, ok, the beneficial part might be stretching it a bit. I think views were create so programmers and database admins don't kill each other Anyhow views in mySQL haven't been around all that long so I started coding around them by de-normalizing the database information to increase performance, this is what works for me.

Good indexes and joining is sometimes just as fast as denormalizing depending on the given situation (that situation no one but you can determine and test for). This is why you see people going back in forth, what works for some will not work for others, some people are more database centric, others are programming centric.

Personally for the stats I would roll them up with the player account just for ease of use and speed. A lot of people say don't make the player table to big, the reason this is said is most make the query "SELECT * FROM x" even though they are only using maybe 10 fields from the x table. SELECT * is bad, only grab the information needed!

Finally, yes, your outline of how xp/leveling should work is the approach we use in a couple different games. I'm not saying it's the absolute correct way but it works for us and can be easily updated

[AcidicOne]

Thanks for the reply, it has given me something to look into aka indexing

but correct me if I am wrong but given the location of the stats wither in the user table, or a stat table, I have two options either SELECT * from user or from stats, would this not be nullified only query the stats needed for given action? would only mean querying 1 table for information when using/displaying user stats as opposed to 2.