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