cdoyle Posted July 1, 2008 Share Posted July 1, 2008 Hi, I've been working on developing an txt based game using ezRPG as my base engine. I created a mod, for those in the hospital. Here is my table struture. I have the players table id (pk) players name etc Weapon ID id (pk) weapon name then I created the hospital table id (pk) Player_dead <<player ID that was killed Player_killed_by << The player ID that did the killing Weapon_ID << What weapon ID was used Time_Left << How much time is left So then in my pages when someone dies, the data is stored in the hospital table. I have a cron that updates the time left, and when it reaches 0, it drops that row from the table. Then I query to pull everything together and display who is dead in my medical ward table. So was pretty happy with this mod, and posted it in the ezRPG forum for others to use. Well one has a replied saying that I've made it too dificult and I should just add to the player table fields for dead (0=alive 1 =dead/weapon_used/time_left to the players table and call it good. Said that it would be more efficient that way??? I guess I'm not seeing how adding more and more fields to the players table, knowing that when a player is alive '3' of those fields will be null or blank, is better? Isn't the whole point of a relational database, is to try and normalize things as best you can? Which is what I was trying to do. it just seems in the long run, a spreadsheet like table that has a bunch of fields per row, that have no data could cause problems later on? What are you throughts, for somethign like this. Would you have a seperate table to store this information. Or would you add 3 more fields to the players table, even tho you would have 3 fields blank when the player isn't dead. Quote Link to comment https://forums.phpfreaks.com/topic/112801-database-design-question/ Share on other sites More sharing options...
Wolphie Posted July 1, 2008 Share Posted July 1, 2008 In terms of BOOLEANS for databases (including relational databases), numeric values are always best. 1 = true, 0 = false. Just like binary. If it goes above 0 and 1, then the same rule still applies. Just use a switch statement. <?php function check_player($pid) { $in_hospital = false; // default $suspend_weapons = false; // default $query = sprintf("SELECT `status` FROM `db_name` . `table_name` WHERE `player_id` = '%s' LIMIT 1", mysql_real_escape_string($pid)); $result = mysql_query($query) or trigger_error(mysql_error()); if(mysql_num_rows($result) > 0) { if($obj = mysql_fetch_object($result)) { $status = $obj->player_id; switch($var) { case 0: $location = 'The player is dead!'; $in_hospital = true; break; case 1: $location = 'The player is alive!'; break; case 2: $location = 'The player is in jail!'; $suspend_weapons = true; break; case 3: // Whatever... break; } } } else { $location = 'Player ID does not exist!'; } return $location; } ?> I don't know if that's sufficient enough. But that's what I would do. I would also construct the hospital table slightly differently too. id pid kpid wid time_left hp (Current hp (should rise in stages) in hospital) I would also have a purchasing history to determine which player has which weapon. Quote Link to comment https://forums.phpfreaks.com/topic/112801-database-design-question/#findComment-579414 Share on other sites More sharing options...
cdoyle Posted July 1, 2008 Author Share Posted July 1, 2008 In terms of BOOLEANS for databases (including relational databases), numeric values are always best. 1 = true, 0 = false. Just like binary. If it goes above 0 and 1, then the same rule still applies. Just use a switch statement. <?php function check_player($pid) { $in_hospital = false; // default $suspend_weapons = false; // default $query = sprintf("SELECT `status` FROM `db_name` . `table_name` WHERE `player_id` = '%s' LIMIT 1", mysql_real_escape_string($pid)); $result = mysql_query($query) or trigger_error(mysql_error()); if(mysql_num_rows($result) > 0) { if($obj = mysql_fetch_object($result)) { $status = $obj->player_id; switch($var) { case 0: $location = 'The player is dead!'; $in_hospital = true; break; case 1: $location = 'The player is alive!'; break; case 2: $location = 'The player is in jail!'; $suspend_weapons = true; break; case 3: // Whatever... break; } } } else { $location = 'Player ID does not exist!'; } return $location; } ?> I don't know if that's sufficient enough. But that's what I would do. I would also construct the hospital table slightly differently too. id pid kpid wid time_left hp (Current hp (should rise in stages) in hospital) I would also have a purchasing history to determine which player has which weapon. Hi, thanks for replying but I'm a little confused. So are you saying that I should add a 'alive' field to the players table? If the player is alive = 0 if dead = 1? But also keep the hospital table? If so, wouldn't that be basically duplicating what I'm doing with the hospital table? And then when the player is dropped from the hospital table, I would then need to run an additional query to update that 'alive' field in the players table. Is that what you meant? I'm not really understanding what you meant. Sorry The HP field is currently in the players table, that's how it comes when you setup the db. I have it set to raise xx amount every 15 minutes I believe, until it reaches maxhp There is a table in the db, that stores which items a players has (Items). Quote Link to comment https://forums.phpfreaks.com/topic/112801-database-design-question/#findComment-579509 Share on other sites More sharing options...
cdoyle Posted July 2, 2008 Author Share Posted July 2, 2008 Does anyone else have any thoughts? I just always thought when you are designing a database, you should try and normalize and split out your tables when possible. From what they are telling me there, I'm just making it 'too difficult' and I should have just added all the columns to the players table. That doesn't seem right too me, and goes against everything I've been learning. If I am correct, what are some of the consequences later on of having a bunch of extra fields added to the main players table? Quote Link to comment https://forums.phpfreaks.com/topic/112801-database-design-question/#findComment-579962 Share on other sites More sharing options...
keeB Posted July 2, 2008 Share Posted July 2, 2008 Any design which requires database cleanup so frequently is silly. Your thoughts on database normalization are correct, but your implementation of normalization leaves a bit to be desired. Basically, instead of a cron job, you should only check to see if the player is alive and well when needed, not on some scheduled interval. I think that is the complexity the user was referring to. Your solution doesn't scale well at all. Quote Link to comment https://forums.phpfreaks.com/topic/112801-database-design-question/#findComment-580265 Share on other sites More sharing options...
cdoyle Posted July 2, 2008 Author Share Posted July 2, 2008 The cron job is to reduce the amount of time left in the hospital. Everytime it runs, it reduces 1 minute from each player in the hospital. Once a players time reaches 0, they are droped from this table, and no longer in the hospital. How else would you do it? Is there another way to reduce a users time in the hospital? If one player is placed into the hospital for 45 minutes, and someone else is 15 minutes. How do I reduce their time as each minute passes? I'm open to suggestions to make this better. What the user is saying is I should have no hospital table at all, instead put all the fields in the player table. So even with their method, I would still have the cron to update as each minute passes. Quote Link to comment https://forums.phpfreaks.com/topic/112801-database-design-question/#findComment-580354 Share on other sites More sharing options...
keeB Posted July 2, 2008 Share Posted July 2, 2008 Well then, you're both wrong. Why decrement a value for EVERYONE every minute when you can just set EXPIRE_DATE in the hospital table? When the user is queried, left outer join on hospital to get his information if needed. if EXPIRE_DATE > TODAY() he's no longer dead. Quote Link to comment https://forums.phpfreaks.com/topic/112801-database-design-question/#findComment-580431 Share on other sites More sharing options...
cdoyle Posted July 2, 2008 Author Share Posted July 2, 2008 It's not updating everyone in the game, only those ID's in the hospital table. I'm not really sure how to implement your suggestion. If a user is killed by another player, and if the weapon that they were defeated with, puts them in the hospital for 45 minutes. Each weapon in the game, puts a player in the hospital for xx amount of time. For example, if they are defeated by someone with no weapons they are only put in for 5 minutes. but if they are defeated by someone with a larger more effective weapon, it could be 45 minutes. While in the hospital, they are limited to only 1 page in the game. The inventory, where my plans are for them to use meds to reduce the amount of time in the hospital. How would I do this using your method? I think I'm misunderstanding, but it seems like with your method, every player is listed in the hospital table? Quote Link to comment https://forums.phpfreaks.com/topic/112801-database-design-question/#findComment-580439 Share on other sites More sharing options...
keeB Posted July 3, 2008 Share Posted July 3, 2008 No. Here's the pseudo code: Request comes in for player $user (could be someone browsing his profile, attacking, I don't know your game, so bear with me) Code loads up entities associated with $user - User table and Hospital table: select * from user u left outer join hospital h on u.userid = h.userid If user entries are in hospital table (you have the record set if so, check what a left outer join [1] is if you don't know) If user is in hospital: check HOSPITAL.EXPIRE_TIME (this was set when the user died) If HOSPITAL.EXPIRE_TIME > TODAY() DELETE FROM HOSPITAL WHERE USERID=$USERID $user->dead = false; ELSE redirect to inventory page $user->dead = true; [1]: http://www.sqlnation.com/ansisql/sql_leftouterjoin.htm Quote Link to comment https://forums.phpfreaks.com/topic/112801-database-design-question/#findComment-581004 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.