Database Design Question

[cdoyle]

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.

 

 

 

[Wolphie]

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.

 

 

[cdoyle]

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).

 

[cdoyle]

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?

 

 

[keeB]

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.

 

[cdoyle]

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.

 

 

 

 

[keeB]

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.

 

[cdoyle]

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?

 

 

 

[keeB]

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