DISQUS

Scion · 1 year ago

I would caution against mixing types in the same field, If you do sooner or later it WILL come back to bite you.

If you want to have some stats that are numeric and some that are text based there are several possible approaches.

One would be to replace the value field with two fields one numeric that other text...and have a third Enum typed field to indicate which of those contains the value.

Another aproach would be too have two seperate tables one for text based stats and the other for numeric stats.


although im not convinced for the need for text based stats...theyre not really stats...theyre..ummm...something else? :)

Luke · 1 year ago

Hi Scion,

I am not quite sure I understand what the benefit of using the two different types of columns would be - especially in a loosely typed language like Perl or PHP. Whether the user retrieves a number or a word, that value will still be loosely-typed after it's been retrieved - which means the issue can bite the user either way. How would changing the number of columns in the tables fix that?

for you health · 9 months ago

nice article! nice site. you're in my rss feed now ;-)
keep it up

Mark · 7 months ago

What method would be faster? Having the stats in your user table is one query less right?

I tend to select all data from the user in my header using a mysql_fetch_array so I can use things like $user['credits'] or $user['id'] wherever I want. Is this a bad habit? I figured you were gonna use the user data on almost every page anyway.

Luke · 7 months ago

Chances are when it comes to performance that selecting your stats directly
from your user table will be faster - however, if you ever want to add(or
remove) a stat you will need to make changes directly to your user table.

Anubis · 6 months ago

It would actually be just as fast to do it this way as in 1 table... for example:

3 tables, users, stats, user_stats (to use the example tables)

I want to grab the stat "Attack" for the user with ID 1.

Attack's ID = 1, User ID = 1. SO

SELECT `user_stats`.`value` FROM `user_stats`,`stats`,`users` WHERE `user_stats`.`userid` = `users`.`id` AND `user_stats`.`statid` = `stats`.`id` WHERE `users`.`id`=1 AND `stats`.`id`=1 LIMIT 1;

And there you have it.

Smitty927 · 1 month ago

When dealing with a larger player base, having a separate table structure for stats only is the way to go! The reason for this? Simple really. If it is in the user table, then you are running queries on the stats data on every page load, it would be more demanding on the database.

To eliminate the need for querying 3 tables, use a select statement similar to what Anubis has pointed out, but simply create a new view of the 3 tables with it and then query that view.

ie:

create view user_stats_view
as
SELECT * FROM `users`, `stats`, `user_stats`
WHERE `user_stats`.`userid` = `users`.`id` AND `user_stats`.`statid` = `stats`.`id`;