Implementing a flexible stats system

[tonald]

http://buildingbrowsergames.com/2008/05/06/building-browsergames-implementing-a-flexible-stats-system-php/

I am sorry that I don't understand the view of author.
Why his method make the updating of stat easier?

In orginal method,
eg users(user_id,password,attack,defence)
When I want to add a field called magic, I can just add a field magic and set default to 50 for example
what's problem is this method?
Thank you

[travo]

The benefit of having the stats in a different table is that simple queries on the users table which do not require all of the users stats (eg for logging in) can be performed without the delay that the extra data would cause if you had many players.

As long as you index your tables properly, you shouldnt notice too much of a difference, most likely nothing, in time between 2 tables using left join, but you will notice the difference if you have all of the stats in the users table if you are logging in or similar.

[Dasein Fiasco]

I think tonald's right to be suspicious of the implementation here. Normalizing stats doesn't really gain you anything that the RDBMS itself doesn't already provide. Sometimes people get too clever in database design and end up creating an RDBMS inside an RDBMS (to a greater or lesser degree), which may feel right from a programmatic approach, but is essentially reinventing what the database engine is already doing while also incurring a performance hit and violating database design conventions, and perhaps what is most important depending on your environment, eliminating any sort of type safety (which will also harm application performance if you constantly have to typecast for simple arithmetic).

But I'm only being critical of the article as presented. It doesn't mention any of the potential benefits of this approach, focusing only on a single mistaken assumption about database design and maintainability. It is simply wrong to imply that normalizing stats is in any way more maintainable than modifying the database through any number of options including (1) the RDBMS software, (2) an IDE, (3) a sql query that is no more difficult than INSERTing a column as in the article's convoluted example, (4) or ideally as a best practice, by running the column addition through a DB migration script.

Now as far as speed, it is almost always going to be slower to normalize a table. Joining a stats table, as travo suggests, will in fact be slower than if the columns were part of the user table. JOINs are the enemy of a performant database. If you're worried about unnecessary columns, you shouldn't be SELECTing * in the first place. But stay away from JOINs if you're concerned about speed. One of the recent trends in web development has been to move away from relational databases towards flater structures. RDBMS isn't declining in use, but from what I understand, even megasites that need to store intricate nested relationships, like Facebook, communicate with the website itself through JOINless queries against view tables. All this means is that JOINs are a serious enough performance concern that there is a species of database development designed to eliminate them, not to mention old tricks like views.

That all said, a reason why you might want to normalize user stats would be if you had a lot of other tables directly affecting stats, such as modifiers, state indicators, advancement logs, etc. That would be reasonable. But not because you think it will make your codebase simpler and more maintainable or because it will enhance performance. In fact those are the very sacrifices you make in order to gain the benefits of more complex, normalized data schema.

[Mufasa]

I too saw how that was laid out and just didn't get the reasoning... but I know that many folks have their own style. I'm with fiasco though, SELECT what you need and only what you need. Even if you need every column, naming them out will be faster than a SELECT *

[travo]

I do agree with that, but if you have all of the stats inside the user table (for example in my game, I have stats about building levels, vehicle levels, etc) that table would become massive and querying it would start to slow down, no matter how many columns you select.

However yes I see what you mean Dasein.

But if I do have say 30 columns in my usrs table, it will just get too slow, so I am forced to use a join. Unless there is another way I cannot see....

[Mufasa]

Well then you would keep important stats in the table, and do something for the others. things like HP, Strength, Defense etc should be on the table IMO.

Things like vehicle levels should be in another table with the vehicle no? i.e. a table called vehicles with "name", "description", "level" etc as columns?

[tonald]

Thank you all of you.
I feel the same with Dasein Fiasco.

[Scion]

One of the reasons for splitting of stats into a seperate table like in that tutorial is to allow flexibility in what stats will be used, that is a set of stats is not preset, so  that in mid design you can easily add additional stats, or that new stats can be added that only affect a new aspect of the game.....Its also importat to realise that the tutorial is targeted at beginers and that the same tutorial should be re-usabel by many people that may go on to base many diferent projects on it. In that case making a flexible base system that can be re-used by all makes sense.

If you have a simple stat design that is almost certainly going to be stabile then id recomend that the stats belong as columns in a table (Im doing this myself)...it may not be the user table directly but going as far as the tutorial is overkill ....however if your game is constantly in flux and new skills, new areas, new abilities are being added regularly then the maintainability and re-use factor of the tutorials approach may be well worth it.

Finally its also important to remember that the DB is the place where you store persistant data...it is not allways necessary to read from the db if you know that you have the same information available in a faster repository (cache)....players stats would be up there at the top of the list of candidates for being cached...

[Dasein Fiasco]

Scion, my point is that the "flexibility" of normalizing user stats is not any more flexible than storing the stats as columns in a user table. In other words, whether or not you decide to normalize stats should have nothing to do with ideas about "flexibility", because the advantages and disadvantages of that approach are found in things like performance, schema design, etc., but not in flexibility.

Maybe it would help to know that (I'm simplifying a bit) behind the scenes the database is already structured in such a way that the tables you see in YOUR database are records in a "Tables" table, and that the columns in YOUR tables are records in a "Columns" table. Relational databases are relational from the ground up, except that as a developer the bottommost implementation has been abstracted away from you and hidden behind the scenes. An "ADD COLUMN" really is an "INSERT" when you get down to it.

The fact that the article targets beginners is all the more reason to be extremely clear, while on the contrary it instead seems to misrepresent database design strategy, emphasize the wrong things, and raises issues that are completely beside the point.

I defy anyone to imagine a scenario where you must add or modify stats in such a way that it is easier to do so against a normalized stats table than to alter a user table directly. At worst, the "flexibility", "maintainability", and "re-use factor" of the article's approach will equal that of direct column manipulation, but I think it most cases it will prove more limiting, to say nothing of performance concerns, typecasting concerns, and other pain points that in my opinion make the notion of any improved maintainability a non-starter unless you *specifically* need to treat stats as a separate data object *for reasons of game mechanics*.

As a cardinal programming rule, you always want to do the simplest thing that works. If none of my other arguments are reaching you, then maybe this principle will make sense. There is no reason to add complexity to your application, especially if you are a beginner and less capable at keeping your codebase organized and clean, unless there is a demonstrable need to do so. The article didn't mention a single valid reason for normalizing stats, and in fact mentioned only a number of incorrect assumptions about database design in specific, and software development in general, and I'm going to keep repeating this until someone gives a valid counter-example. Citing "flexibility" is NOT enough. HOW is one approach more flexible than another, or WHY is it not? I think I've been clear about the specific technical reasons the article is wrong, but I haven't heard any technical objections.

[Mufasa]

IMO, the only "flexibility" offered, is ease of stat management when you're exporting your script to be reused. In this scenario, it'd be pretty easy to build an admin panel where you could add and delete stats.

Still though... as has been said... it's not the best way of doing it. Columns FTW.

[karnedge]

Still though... as has been said... it's not the best way of doing it. Columns FTW.

This all makes sense... I do have a question or two though:

Would it be a good idea to have your users table with peritanant info then another table with columns of stats... and matching id's or just throw all the stats in columns on the users table?

Then, another question would be... does it lower performance when loading several rows over and over or when you have too many columns? With that Tutorial... I also thought it was saying that basically at the heart of it all... "we do not want a bunch of columns in our table because it make the game slower"?

I have around 21 stats, per user and about that per 'monster', then about 10 stats per item in the game... would it make sense to do a seperate table for stats in columns for the users so i do have a table with 30 columns haha?

[Mufasa]

Would it be a good idea to have your users table with peritanant info then another table with columns of stats... and matching id's or just throw all the stats in columns on the users table?

If you mean like a users table, then like a users_profile table with stuff like forum_signature, profile_blurb, AIM etc linked by a userid, I think yes. The profile data won't be called often so it's OK to just select it from that other table when needed IMO.

I just wouldn't do it as a users_attributes table with columns attribute, value and then rows that have things like signature -> value, blurb -> value etc as the original article suggests.

Then, another question would be... does it lower performance when loading several rows over and over or when you have too many columns? With that Tutorial... I also thought it was saying that basically at the heart of it all... "we do not want a bunch of columns in our table because it make the game slower"?

One would need to benchmark, but I'd imagine selecting one row from a table based on userid is better than selecting many rows from a table and linking them by userid.

I have around 21 stats, per user and about that per 'monster', then about 10 stats per item in the game... would it make sense to do a seperate table for stats in columns for the users so i do have a table with 30 columns haha?

I'd do it that way... have a table called monsters and all their stats as columns. If all monsters will have it then that's the way to go.

A scenario where I'd use the original article's method would be when I have something like a categories table, and even though each category may have multiple subcategories, I wouldn't put them as columns, I'd have them as their own entry and linked by a parentid column.

[karnedge]

If you mean like a users table, then like a users_profile table with stuff like forum_signature, profile_blurb, AIM etc linked by a userid, I think yes. The profile data won't be called often so it's OK to just select it from that other table when needed IMO.

I just wouldn't do it as a users_attributes table with columns attribute, value and then rows that have things like signature -> value, blurb -> value etc as the original article suggests.

Yeah that sounds a lot better anyway because with this "flexible stats system" I am constantly querying one row at a time to get each stat. If i put them into columns, I can grab all the stats at once with a single SELECT query instead of 20 haha. I mean it'll be rare for the game not to use most of the stats at anytime or even just to display them.

One would need to benchmark, but I'd imagine selecting one row from a table based on userid is better than selecting many rows from a table and linking them by userid.

QFT... like I said, the logic of doing a single SELECT query on 1 row with multiple columns for all your stats makes more sense than doing a SELECT query for each and every stat that you need.

I'd do it that way... have a table called monsters and all their stats as columns. If all monsters will have it then that's the way to go.

A scenario where I'd use the original article's method would be when I have something like a categories table, and even though each category may have multiple subcategories, I wouldn't put them as columns, I'd have them as their own entry and linked by a parentid column.

I think I'll do the following:
Tables - Columns
users - id, username, password, email, token, etc
user_profiles - user_id, signature, location, turnons, whatever
user_stats - user_id, stat1, stat2, stat3, etc
monsters - id, name, stat1, stat2, stat3, etc
items - id, name, stat1, stat2, stat3, etc

that seems most logical i think in a basic sense... right?

[Mufasa]

Yeah at this point it comes down to preference. I actually have a user class that gathers all the info so my setup doesn't matter too much (as far as in-page code is concerned), but I keep my stats in the table i.e. hp, hp_max etc.

It makes the table a bit longer (column-wise) but since I'm pretty much using most, if not all, of those fields on each page, why not.

[karnedge]

It makes the table a bit longer (column-wise) but since I'm pretty much using most, if not all, of those fields on each page, why not.

Yeah, I see what you mean. You're rarely going to just grab just one stat if not ever.

So I think this thread comes down to stating that the database shape would more likely make sense just make a table for user stats in columns. You can just as easily add another column later with a default int set when you add it. It would be just as flexible.

You may want to use the BuildingBrowsergames.com flexible stats system if you are going to constantly change your games stats, adding and removing or changing names, etc.

[xBleuWolfx]

So wait. What you guys are saying here is that instead of using:

Code: [Select]

function getStat($statName,$userID) {
require_once 'config.php';
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
or die ('Error connecting to mysql');
mysql_select_db($dbname);
$query = sprintf("SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND user_id = '%s'",
mysql_real_escape_string($statName),
mysql_real_escape_string($statName),
mysql_real_escape_string($userID));
$result = mysql_query($query);
list($value) = mysql_fetch_row($result);
return $value;
}

to get the stat, we should use something like:

Code: [Select]

function getStat($userID) {
require_once 'config.php';
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
or die ('Error connecting to mysql');
mysql_select_db($dbname);
$query = sprintf("SELECT value1, value2 FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE value1Name = 'value1Namey' AND value2Name = 'value2Namey') AND user_id = '%s'",
mysql_real_escape_string($userID));
$result = mysql_query($query);
list($value) = mysql_fetch_row($result);
return $value;
}


[Mufasa]

Well ultimately it comes down to how your code is set up. I have a user class, and when it's initialized (and passed a user ID which is cleaned to an INT) it just goes:

SELECT username, avatar, hp, hp_max, strength, defense, speed, ... FROM users WHERE id = $userid

Then the variables are stored in my user class and any time I need to access them within my code I can simply go:

$attack = $user->strength - $opponent->defense;

You'll notice that the class is generic so if I'm having a battle I can instantiate two user classes ($user and $opponent), pass them individual IDs and then just use them. This way you fetch the data ONCE and use it when needed inside the code.

[karnedge]

Well ultimately it comes down to how your code is set up. I have a user class, and when it's initialized (and passed a user ID which is cleaned to an INT) it just goes:



Then the variables are stored in my user class and any time I need to access them within my code I can simply go:

$attack = $user->strength - $opponent->defense;

You'll notice that the class is generic so if I'm having a battle I can instantiate two user classes ($user and $opponent), pass them individual IDs and then just use them. This way you fetch the data ONCE and use it when needed inside the code.

I really like that idea... would it be something like this:

Code: [Select]

class User {
var $username;
var $strength;
var $defense;

function User(){
$this->initStats($session->userid);
}

function initStats($userid){
$query = sprintf("SELECT username, strength, defense FROM users WHERE id = $userid");
$result = mysql_query($query);
$stats = mysql_fetch_assoc($result);
$this->username = $stats['username'];
$this->strength = $stats['strength'];
$this->defense = $stats['defense'];
}
};

$user = new User;

Is that about right... the main reason I ask for clarification is because I didn't know that those variables stay for the life of the session? so if you wanted them to redo variables again,  you would have to do the $user->initStats() again right?

[Mufasa]

It's really up to you how to do it. You can store variables in the session then re-fetch them on pageload, but for variables that can change often (like hp, defense [if they train] etc), you may want to fetch each time -- just to make sure you're working with the latest.

Things like avatar, username etc can be fetched once and only refetched if it changes

[codestryke]

I really like that idea... would it be something like this:

Code: [Select]

class User {
var $username;
var $strength;
var $defense;

function User(){
$this->initStats($session->userid);
}

function initStats($userid){
$query = sprintf("SELECT username, strength, defense FROM users WHERE id = $userid");
$result = mysql_query($query);
$stats = mysql_fetch_assoc($result);
$this->username = $stats['username'];
$this->strength = $stats['strength'];
$this->defense = $stats['defense'];
}
};
$user = new User;


Very close but no.. You don't want the constructor to load the value from the session. The reason being is if you have an attack system you would want to load two player classes like Mufasa said. As it is now every time you init the class it'll be to the current session...

Your constructor, instead, would look something like

Code: [Select]

function User($id) {
  $this->initStats($id);
}

Then to init the class it would be
$player = new User($_SESSION['userid']);

Then during the battle you then can do
$opponent = new User($opponentid);

Now you have two classes one for the attacker (player) and the defender (opponent) that you can retrieve stats information from.

However

I wrote about this a long while back but I'll repeat it now. This is a nice clean and tidy way to code but when it comes to attack systems it will cause havoc if the game is mildly popular with a bunch of players online. Let me give you an example:

Bob attacks John, John is online and actively playing the game. When you load John's record everything for that instance is static for the attack. When you write the information back after the attack that information could of changed while John was off playing other aspects of the game which causes an over write of the saved information.

Two ways to get around this:
1. Put a flag in the player account, when the attack happens mark the flag in the opponent record. If the opponent tries to do something in the game while being attacked it stops them with 'You are being attacked'. Once the attack is finished you clear the flag on the opponent.

2. Use your class ONLY for loading purposes. When it comes to saving the data to the database always do a 'stat = stat + $bonus' that way you update the latest information.

Hope this helps

[Scion]

@Dasein

A point i find myself repeating all the time is that one needs to be pragmatic, choose the right tool for the job, In many cases, stats in columns will be the best choice, As i said i do it that way myself, but there may well be situations where the other approach is better.

Of the top of my head,,,,imagine if during game play, users, or content generators are able to generate custom items that can have a number of stats... then i dont want to be going and doing schema modification querries while the game is up and running...(shudders) But then we can probably sit here all day and come up with situations or examples where each approach is sub-optimal....hece my pragmatism....never ever rule out any approach....

You may well be right that it is not ideal for a beginners tutorial...but then hey....if the tutorial sux you could allways write a better one...im sure Luke would happily post it on the buildingbrowsergames site

[karnedge]

Then to init the class it would be
$player = new User($_SESSION['userid']);

Then during the battle you then can do
$opponent = new User($opponentid);

I am still learning to use OOC (if that's even right ha) but when I do all this with those variables for some reason, it's giving me warnings:
Notice: Undefined variable: _SESSION in \www\include\user.php on line 74
Notice: Undefined variable: opponentid in \www\include\user.php on line 75

Even though I use _SESSION everywhere else... and the userid is definitely being assigned to the session... and on my index, I have the user.php included with html showing the stats: $player->attack etc.

[Harkins]

You have to call session_start() before the $_SESSION superglobal becomes available.

The other one... you're not assigning the value of $opponentid before you use it. Add a'$opponentid = ...' somewhere above where you're creating your User object.

[karnedge]

omg, I've totally confused myself now... before this Stats OOC thing, I had a session and database classes. Session's constructor set the time and started the session doing the session_start(): and checking login status and then other account changing events. the database class handles anything database queries. both of these classes at the end have simple inits:
$session = new Session;
$database = new MySQLDB;

with this Stats thing, I have $player, $opponent, $item to pull stats for. I dont know why but I can't figure out how to use it correctly. I have my stats query being echoed, and its showing that no matter what, all my stuff is queried including the opponent even though, I dont need it to.. ugh, I dont know how to explain it:

Code: [Select]

Notice: Undefined variable: _SESSION in \www\include\user.php on line 76
QUERY: SELECT * FROM stats WHERE id = ''

Notice: A session had already been started - ignoring session_start() in \www\include\user.php on line 34
QUERY: SELECT * FROM stats WHERE id = '0'

Notice: A session had already been started - ignoring session_start() in \www\include\user.php on line 34
QUERY: SELECT * FROM items WHERE id = '0'

Notice: A session had already been started - ignoring session_start() in \www\include\user.php on line 34
QUERY: SELECT * FROM targets WHERE id = '0'

Notice: A session had already been started - ignoring session_start() in \www\include\session.php on line 27
I was hoping to make simple looking like my session and database classes but I dont know how to create different objects and use variables with them.

Code: [Select]

$id = 0;
$player = new Stats($_SESSION['userid']);
$opponent = new Stats($id);
$item = new Stats($id);
$target = new Stats($id);
Anyway, maybe all this sounds dumb but I'm not getting it. I put the "$id = 0;" part so that it would stop saying "Undefined variable $opponentid" etc. Am I suppose to put the Objects somewhere else instead of after the Stats class?

[tonald]

Sorry, I am a beginner
What is IMO table?

Should I have this?
Users_basic(user_id,password)
Users_stats(att,def....)
.....