Best Way To Handle Battle Records

[Aleeious]

Getting close to completing my battlemail script. Battles are handled as so:

1)user enters person to battle, 6 attack and 6 block moves

2)Defender responds by accepting the challenge and doing the same

3)Afterwards both are treated to some sweet kung fu action where the winner is shown based on the moves each picked.

The moves of each user are gonna be stored in a seperate field like so:

Code: Select all

123213:132121
attack 1, attack 2, attack 3, attack 4, attack 5, attack 6:block 1, block 2, block 3, block 4, block 5, block 6

the same is repeated for the defenders move field

My problem is how do i handle if a match is completed or not? My original plan was to have the row moved from table "matches_open" to another table "matches_completed" but, it seems like a waste to have 2 identical tables. The other idea is to have 1 table with a field called "completed", but i would imagine how long it would take to go through millions of rows checking that field as opposed to having a smaller separate one. Any assistance in this matter would be greatly appreciated.

Sincerely,

Aleeious

[kaos78414]

Honestly both of those methods are fine, but keep in mind that the update of a combat to completed with the flag only takes updating a single field, while moving it to another table requires a delete and an insert.

Why would you have to go through millions of rows checking the completed field? I imagine proper optimization will eliminate most of your concerns. http://www.techrepublic.com/article/thr ... queries/#.

[MikuzA]

I would go for a table that contains the unique id, players id, match id, status (int).
then just index the player id and status.

So if you use the player id and status in a where sentence, it doesn't have to do a full table scan.

oh and status would go somehow like, 0 = Open, 1 = Completed. (and 2 = Postponed, 3 = Cancelled, 4 = Error etc.)

The reason for this is that in a possible heavy weight query, check what you actually need - (do not use always select * from) and index those columns if necessary.

[Aleeious]

I would go for a table that contains the unique id, players id, match id, status (int).
then just index the player id and status.

So if you use the player id and status in a where sentence, it doesn't have to do a full table scan.

oh and status would go somehow like, 0 = Open, 1 = Completed. (and 2 = Postponed, 3 = Cancelled, 4 = Error etc.)

The reason for this is that in a possible heavy weight query, check what you actually need - (do not use always select * from) and index those columns if necessary.

So your saying use 2 tables like so:

Code: Select all

Table matches_index
int id, varchar(16) player1, varchar(16) player2, int matchid, int status

Then

Code: Select all

Table matches
int id, varchar(13) challengermoveset, varchar(13) defendermoveset

Is this correct? Any assistance in this matter would be greatly appreciated.

Sincerely,

Aleeious

[MikuzA]

I would go for a table that contains the unique id, players id, match id, status (int).
then just index the player id and status.

So if you use the player id and status in a where sentence, it doesn't have to do a full table scan.

oh and status would go somehow like, 0 = Open, 1 = Completed. (and 2 = Postponed, 3 = Cancelled, 4 = Error etc.)

The reason for this is that in a possible heavy weight query, check what you actually need - (do not use always select * from) and index those columns if necessary.

So your saying use 2 tables like so:

Code: Select all

Table matches_index
int id, varchar(16) player1, varchar(16) player2, int matchid, int status

Then

Code: Select all

Table matches
int id, varchar(13) challengermoveset, varchar(13) defendermoveset

Is this correct? Any assistance in this matter would be greatly appreciated.

Sincerely,

Aleeious

Yeah, something like that yes.

Or simplify your matches_index by having just one instance of the player_id.

Code: Select all

Table matches_index
int id, varchar(16) player, int matchid, int status

Then just have two instances of the same match, then when the match is completed you can do a update per matchid.
If you build this way, you can add more than 2 players in future, for like some kind of team vs team fights.

Sorry but I'm always thinking of new features! But your example works fine.
Oh and there is no need to keep your database in low row count, you can handle 10 million rows in milliseconds if the table is just created right and you are querying it in an optimized way.