sniko Posted March 11, 2012 Share Posted March 11, 2012 (edited) Although this has been pointed out within the community numerous times, I thought I'd write an 'article' on it... Within McCodes Version 2 - Unsure with other versions, the database table designs are poor, in my opinion. Let me explain; Let's look at the most infamous table of them all, `users`. In one of the main files, which are called upon on every request a user makes on your site globals.php, everything (All columns) are selected - with a condition, a userid session, set when you log in - authenticate.php. So, In the base McCodes, it would select 68 columns, on every single request the user makes, just from the users table. You may think, but I need all those columns, and having a Left Join will simply do no help. So, let's take a little snippet from the `users` table. course cdays gang daysingang jobrank fedjail forumban fb_reason friend_count enemy_count boxes_opened As all of these are default 0, and rely on other systems for a change, why don't we create a new table for each (grouping relevant ones together) and having `userid` as the foreign key, to link. Here's my reasoning; As stated before, they rely on other systems for a change, so what happens if they don't use that specific system whilst playing, for example, they never get forum banned, or they don't donate so their friend or enemy count won't go up (pretty useless anyway, as you can use mysql_num_rows or count() with a specific condition) so this means that every time a user makes a request (Clicks a link on your game, to another page) a query to the database is made, which selects these 'redundant' columns, wasting space, and resources. So here's what I propose; We, developers, game owners, whoever, makes a table for these columns (grouping relevant ones together) to save resources and space, possibly resulting in saving money. Here's one for courses The SQL This will take out the two 'redundant' columns from the `users` table, resulting in only 66 columns being selected at each request from the `users` table. 1. Insert data into the new table `users_courses` when they start a new course 2. Select the data when needed 3. Use mysql_free_result once finished with selecting from the new table to free memory Now, if we did this, or better yet, it was set up like this, we'd be saving a lot of resources each request a user makes. Shame it wasn't set up like this, as it would take a while to implement, especially for games that have been edited, with new features/systems. So, as a community, here's what I propose; Whenever we create a system, for McCodes, or not, we try our very best to not insert more columns into the `users` table, but create a new table, like the one I did for `users_courses`. I'm not telling you what to do, I'm advising you, as users, In my view, will get a better playing experience, from a faster response. Your views? -sniko Systems Courses Edited March 11, 2012 by sniko Quote Link to comment Share on other sites More sharing options...
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.