NEW: MYSQL security and Indexing advance database

[Jackolantern]

If you are wanting to make dynamic database that could have many, many different column names, there are a couple of options. The most simple database setup would have your columns looking like this:

ID | A | ValForA | B | ValForB | ......

Where you would store whatever you wanted in A, such as "inc_mp", "fireDmg", or whatever, and then add the numerical value in the "ValFor%" field. Then probably the best thing to do would be to make another table that is filled with the strings you are going to store in A, B, C, and so on so that you can simply put a numerical ID in them instead of putting actual strings of text (since doing that is very error-prone and would require a lot of outside note-keeping and checking to ensure you don't slowly change things as you go).

Another major downside to this is that you have lost named columns. So if you want to find out if the player has a "plus_intel" effect, you have to iterate through everything with a FOR loop to try to find it, and then read the corresponding Val field with its value. This is going to be a major pain.

A more difficult setup with a steep learning curve first, and then easier usage later would be the Entity-Attribute-Value model. With some extra setup, it can handle dynamic tables pretty well.

But then for a game, probably the best thing to do is make a hard-coded, long list of all possible values, and simply make them all column names. Since most of it will involve stats, there really isn't an infinite number of things it could be. Even if you end up with 60 or 70 columns, you can just group them and split them among a couple of different tables. Truly dynamic tables in SQL involve a lot of work!

[Jackolantern]

Typically more tables with fewer columns is better. Certain joins cause MySQL to create virtual tables where each column is matched up with every other column of the other table, so having many columns can slow things down.

That being said, your DB must be very busy for this to start slowing things up, so for now I would say to stick to whatever you feel most comfortable with, while trying to stay on the side of more tables with fewer columns. Needing to join many tables for every piece of data you need can make SQL queries very ugly to write and difficult to understand. So there is a balance!

[Jackolantern]

Best of luck with it and no problem!

[Sharlenwar]

Man, this is a good topic. I'm following along and am learning myself from what you are doing. Thanks for this!

[Jackolantern]

Inserts and Updates are pretty rare compared to Selects, and Selects are sped up by indexing. Not to mention that the speed hit for indexing on Inserts and Updates is basically unnoticeable, so it is almost always a win-win

[jwalton922]

This is probably slightly off-topic, but you may consider using a schemaless DB such as MongoDB.

MongoDB is one of the NoSQL dbs that has become pretty popular recently. MongoDB stores "records" not rows. Mongo stores key value pairs, in which your value can be another object. You have to be somewhat smart with how you design your tables (called collections in MongoDB), but for what you want to do, you'd probably denormalize your data so that it will have better read performance.

I'm at work, otherwise I'd go into more detail, but if you are interested let me know, and I can try to give you some more information.

so a mongo record for you might look like:
(Sorry I skimmed the previous posts so I don't have your actual data in here)
{type : armor, slot: helmet, armor_value: 12, requirements : {strength : 5, dexterity : 12, level : 10}}
stored right next to a record like this:
{type : potion, bonus : {strength: 3, intelligence : 5 }}

I use MongoDB at work, and not having to define a schema is nice. However, joins in mongodb are not as nice as SQL, but you can denormalize your data easily so it balances out.

[leruman]

Interesting subject, i had some similar dilemma when i started working on my game.

First for size of table, i agree that lot of columns are not good. My general idea for equipment system is like following

One main table where all equipment is listed. There exists columns which separate equipment based on type, body slot and similar. There is usually 3 or 4 of these columns, they are generally used in select statement for filtering equipment. These columns are linked to external table where actual definition of possible option are defined, so if you want to add new category just add it in external table, do not need to change in main table. It is 1-n relation between main equipment table and those additional tables.

For effect is best to create relation n-n. You need two more tables for that. In one table you create list of possible effects which can be found in equipment, but without bonuses. In second table you create connection between one specific row in equipment table and one row in effects table, and in that connection you can define exact value of effect.
For example you have long sword in equipment table. In effect table you have magical bonus to attack like effect. In linking table you create row that links these effects and add actual value of bonus.

Benefits that i see in this system are following:
- One universal equipment table
- Universal tables for categories for equipment, easy to add new categories or change existing
- One table for all effects on equipment
- If you change definition of one effect it changes in all equipment
- You can have unlimited number of bonuses for each peace of equipment.

This is my idea how this should be solved, hope that this can be useful to someone.

[Luke111]

Hi! May I recommend using a class that I have written and use in a lot of my PHP websites, that makes it easier and safer to do your ordinary select/insert/update/delete

Here is the code for this class (extension of mysqli) and below the class is an example of how to use it.

Code: Select all

//TheGamerBoard MySQLi Extension to allow for easier access
class mysqli_tgb extends mysqli
    {
        //insert/update/delete function
        public function iud() {
            $args = func_get_args();
            $sql = array_shift($args);
            assert($stmt = $this->prepare($sql));
            if (count($args) > 0) {
                $types = str_repeat('s',count($args));
                array_unshift($args,$types);
                call_user_func_array(array($stmt, 'bind_param'),&$args);
            }
            assert($stmt->execute());
            if (isset($stmt->insert_id)) {
                $toret = $stmt->insert_id;
            } else {
                $toret = 0;
            }
            $stmt->close();
            return $toret;
        }
        //select function
        public function s() {
            $args = func_get_args();
            assert($sql = $this->prepare(array_shift($args)));
            if (count($args) > 0) {
                array_unshift($args,str_repeat("s",count($args)));
                call_user_func_array(array($sql, 'bind_param'),&$args);
            }
            assert($sql->execute());
            $result = $sql->result_metadata();
            $fields = array();
            while ($field = $result->fetch_field()) {
                $name = $field->name;
                $fields[$name] =&$$name;
            }
            call_user_func_array(array($sql,'bind_result'),$fields);
            $results = array();
            while ($sql->fetch()) {
                $temp = array();
                foreach ($fields as $key => $val) {
                    $temp[$key] = $val;
                }
                array_push($results,$temp);
            }
            $sql->free_result();
            $sql->close();
            return $results;
        }
    }
 

and here is how to use it:

first, to set up an object.

Code: Select all

function ConnectDB($host,$username,$password,$database) {
    return new mysqli_tgb($host,$username,$password,$database);
}

global $con;
$con = ConnectDB("localhost","MyUsername","MyPassword","MyDatabaseName");
 

to select, say, all rows from the table users where ID > $x, and print their usernames we would do this:

Code: Select all

$usernames = $con->s("SELECT `username` FROM `users` WHERE `ID`>?",$x);
for ($y = 0; $y < count($usernames); $y++)
{
    echo $usernames[$y]['username'];
}
 

Let me explain this a little bit. it stores all rows selected into an array, with an index starting at 0. so $usernames[0]['username'] is the username of the first user's row returned, $usernames[1]['username'] is the second user's row returned, and so on so forth until the total amount of rows returned minus one. the ['username'] can be anything, as long as it was selected from the database.

Now for a delete statement.

Code: Select all

$con->iud("DELETE FROM `users` WHERE `ID`=?",$x);
 

this deletes the user who's ID is $x.

inserting statements are pretty much the same, just remember to always use the ? for each thing to put in the database that is entered by the user. this stops SQL injection. and for every ?, you place an argument in after the query string. Now when inserting statements, it returns a value. It returns the ID of the inserted row.

I hope this helps. Your code will be so, so much more secure and look far, far better, IMO.

If you have any questions, I am here to answer.

[Luke111]

Hi Oroton!

If you would like me to convert a source file for you, I would be more than happy to help you out. I would also be more than happy to help you out for free securing some of your code, whether you use my method or not.

You can set all the template variables in a for loop, if you are using something like smarty, which supports arrays in templates. However, if you are using a homebrew system, that may be a little more difficult.

Thanks
-Luke

[Luke111]

Hi Oroton!

I have looked through some of the files, and like what you're doing. I can see things that I would personally change, but I don't want to recommend them, as you have a setup you like.

Here are some things that really pop out to me:
1. You use MD5 without salt. This means that if a hacker got ahold of your database, say goodbye to all your user's password security, in just minutes. Think about using a salting algorithm, like say,

Code: Select all

sha1(md5(password) . "myspecialsalt");
 

at least. You could update your user's passwords to use this, so something like

Code: Select all

UPDATE `table` SET `table`.`password`='" . sha1($currentHashedPassword . "myspecialsalt") . "' WHERE `table`.`ID`='$id'
 

should work.

Another thing I recommend is even if you don't use my class, try to learn prepared statements to some extent. They may save you some hassle later if you use them. Even if you don't use my class but use the prepared statement api directly, you can use the same prepared statement over and over again with different variables before closing it.

Thanks!
-Luke