Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Unanswered: Having trouble conceptualizing data

    I have a rather simple question that has been puzzling me.

    I will use boxing as an example, because it illustrates quite well the problem that I have.

    Let's say there are 4 total punches that can be thrown-- a jab, cross, hook, and uppercut.

    There are then two locations they can be thrown to: head and body.

    There are different actions that can occur for that action: hit, blocked, dodged.

    There are two other identifiers: weak and powerful.

    So let us say the first punch of a bout is from opponent one. He throws a weak jab to the head that is dodged.

    Does that mean that every single punch in my database will have it's own unique ID? Wouldn't this database grow quite large even with a rather small amount of fights if each punch has to have its own ID?

    I am sorry if I am completely confused, I promise I tried to search the internet before posting this, but really couldn't find anything that really hammered it down for me.

    Thank you for your time,

    Symbolic

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the identity of a punch doesn't have to hinge on the use of an ID

    so far, nothing you've described requires anything more complex than the actual punch name as the primary key of the 4 different punches

    but to distinguish these from the punch delivered, call them punch types

    so the punch delivered will be one of the 4 punch types, it will have other attributes drawn from other tables -- e.g. strength type weak or powerful, action type hit, blocked, or dodged, etc.

    so far, nothing you've described requires anything more complex than the actual names used as primary keys of the various attribute types, and as foreign keys in the table that records the puches delivered

    where did you get the idea that you needed IDs?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    42
    I think it is related to online gaming. If so,

    I am using silverlight with postgres backend, what we do is

    we store every bout action in a temporary table. Yes every single action of all opponents without a care of how many there are

    However what we care about is storing the final result of such a bout into a main table as soon as the bout ends.

    The temporary table data is always cleared by a background process for a bout that has expired. This process runs indefinetly after a period of 60 minutes from previous clearance. such an action keeps the temporary table size limited. Vaccum full commands are run atleast once a day. and Backups are performed after every vaccum

  4. #4
    Join Date
    Sep 2012
    Posts
    3
    I guess I'm having difficulty understanding how they will be stored.

    The way I envision the database is that I will have separate tables for punch type, punch power, punch location, and punch action type

    So if a person throws a weak jab to the head that is dodged I envision I would need to add "1" to each location and then tie them all together with a foreign key that links them all to the same punch in a certain bout during a certain event.

    So by the end of the fight you might have:
    10 jabs, 4 crosses, 2 hooks, 1 uppercut with
    12 weak, 5 powerful
    15 to the head, 2 to the body
    5 dodged, 5 blocked, and 7 hit

    That is why I think each punch would need a separate ID. How could you know that 1 of those jabs were weak to the body and hit the opponent if you didn't have foreign keys that linked them all together?

    Maybe I have a fundamental misunderstanding of relational databases (likely), but I can't fathom another way to do it and keep track of actions.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Symbolic View Post
    The way I envision the database is that I will have separate tables for punch type, punch power, punch location, and punch action type
    so far, so good

    what you're missing is the "punches delivered" table --
    Code:
    type      power     location  action
    jab       weak      head      blocked
    cross     powerful  body      hit 
    uppercut  weak      head      blocked
    uppercut  powerful  head      dodged
    so you're not actually "adding 1" anywhere, but if you want totals, you just use sql to count them for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2011
    Posts
    42
    Well as i understand, you are not storing the game progress, but storing game status

    we store it in form of game progress in a generic table with all the columns.

    Let's say a tournament stats with four players
    each player has 100hitpoints
    player 1 makes a strike (we refer to a chart to convert blows to hitpoints with random-range %)
    player 2 has 92HP, and returns a light blow, so player1 has 98hp


    In Game Progress, we define it as
    ROW Player 1 HP Player 2 HP Strikefrom StrikeTo ReturnStrikeHP Round TimeStamp StrikeMethod
    1 100 100 0 0 0 0
    2 98 92 P1 P2 2 1

    To find if tournament has ended we check 3 players have zero hitpoints Or gametime has elapsed a stipulated timeout value from last progress

    our table has a primarykey as tournamentid a random 32 character code on basis of playerids

    strikemethod is a combination of player1Hit and player2Hit with a underscore in between

    There are other columns such as armor , mana, spells , which all get carried forward for each player , in total there are 48 columns - 12 for each player and their attributes

    Finally there is a gameResult table that has state of a tournament , with tournamentid, tournament_timestamp, gameresult (complete, PlayerSurrender, InvalidSession , SecurityHack, TimeOut), winnerPlayer , playerMetrics , rowIDfrom, rowIDto

    The also helps in checking if a player has not opened multiple game sessions

    whereas in game status
    Inplace of goals achieved, you place emphasis on gameplay methods

  7. #7
    Join Date
    Sep 2012
    Posts
    3
    Alright, I think I finally understand.

    type, power, location, and action never have values assigned to them other than the description. It is the fifth table (punches delivered) that has all of the other tables within it (linked to foreign keys?).

    If I am understanding this correctly I think I can finally get an accurate schema drawn up. I appreciate the help tremendously and if I find myself struggling with any other issues I will not hesitate to buy your book.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •