Database setup: Troop movement

[Jesterhead]

Hello everyone,

It's been a while... but I decided to give this a go again! (for some reason I always quit my projects after a certain time.)
This being said, up to my question.

Obviously one of the most important parts of a (managing?) browsergame are troops.
Managing troops in your database isn't hard at all, yet concerning troop movement I need some help.

1. I create a separate table for troop movement.

Code: [Select]

Troops
ID | Location | Unit 1->x

Troop_Movement
ID | From | To | Unit 1->x | Action | Duration

Pretty straight forward.

Everytime troops are sent a new record is created.
Once the troops arrive (time = initial time + duration) the record is removed and the troops are added to the local troops.

The problem here is that you can't see the diffrence between reinforced troops and your own.
(Meaning the location owner just gets your troops, you can't take them back etc...)

2. Split Troops

Code: [Select]

Troops
ID | Location | Unit 1->x | Action | Duration

Once troops are send out, the original troop record is reduced, and a new record is created.
When the troops arrive there will be multiple troops at 1 location.

With this method, you send out your troops (split rows) and when they get back you join them again together and remove the empty one.

Problem: A little chaotic? I'm sure there is something easier.

3. 1 row per unit

Code: [Select]

Troops
ID | Location | Unit_ID | Action | Duration

Pretty straight forward, but this is a waste of database space?

Does anyone here have any other ideas/suggestions? They are highly appreciated.

Kind Regards,
Jesterhead

[133794m3r]

If you're moving troops to someone else, as in the 'owner' of the area. I'd just add them to the persons table with a timestamp on that. And that timestamp is used by your code so that it's got to be Now()+some_amount_of_time before they can be used. Or you could just put that as the value. So that it shows that they can only be used if their value is less than the current time.

I'd do it something like this.

Code: [Select]

players_troops(
id int unsigned not null,
troop_type tinyint unsigned not null,
use_after int unsigned not null,
owner_id int unsigned not null,
area smallint unsigned not null,
primary key(id),
index(owner_id)
);
or i'd do it as you said with 'area owner'.

So that they're a table per area(more tables but i guess it would be kind of easier). But yeah #3 is probably your 'best bet' if you wanted them to have actual 'actions' applied to them. But i'd create another table for their actions that way you can have units that are doing nothing and can purge them once they're done.

So it'd be something like.

Code: [Select]

troops_action(
id int unsigned not null,
troop_id int unsigned not null,
action_type smallint unsigned not null,
action_time int unsigned not null,
primary key(id),
index(troop_id)
);
It's not that much of a waste of database space so long as you chose your data-types properly. And then you could update the action table so that they're moving, unless you want to juts have them as one table. And even then you should just change the 'area' or location column since it'd not be a huge waste of data. So long as the columns themselves are as small as they can be, it'd not be a huge waste of space.

Anyway, to summarize, the last one would probably be the best one, since it's straightforward, and it also doesn't use much space.  Since you're having them all as one, so long as you remember to change the 'action' column as needed and the duration thing so long as it's a timestamp. I don't know if chris will come in and say that it's too much space, but basically if you're doing it all as one. You're just having to add another 4 bytes to the record. And 4 bytes whilst semi-big is the easiest way to go about it. Since there's less things to go wrong.

[Chris]

I don't know if chris will come in and say that it's too much space, but basically if you're doing it all as one.

If you have 1000 units and you keep 2 groups, one at home at one on the move, then using #2 you need 2 table rows. If you do it #1 you need 1000 table rows. That's 500 times more Now multiply it by mere 100 players (note you need to check these tables all the time because of arrival times). It is not about database space but CPU power, it will kill the server. #3 is out of question in my opinion.

Personally I would go for armies (containers with units and target location). Players would only create armies and assign orders to them not to individual units (as a nice bonus you can make assignment of generals to your armies). So it's kind of like #2 but manually created by the player.

[Jesterhead]

heh chris, that was exactly what I thought about.

Let players assign troops (legions) and move them all by once.
This was it's also a lot easier to set your battle setup.

But how do you guys think Travian handles this?
(sidenote: I'm NOT making a travian'ish game... but more info on that when it's done)

[saljutin]

Travian

1 table for troops
id/user/from/to/time/unit1/unit2/...

then something similar like
if time = 0 then troop stationed @ from
if time > time() then troop is moving; time()-time=how much seconds to arrive
if time <= time() then update; set time=0, set from=to, set to=''

UPDATE only if logged user can SEE it
philosophy - if you are talking with friend in bar, no need to see if some unknown kids have stolen candy in shop 20km away

[Jesterhead]

Yes I understand.

But let's say you have 20 scouts at 1 location. And you send 1 out to another location, the others stay.
How would you handle this without creating a new row?

[saljutin]

easiest way to do that is to create new row and imo most effective one

[BlackScorp]

what about to group your troops? for example, you select the target and you going to attack it, you select some of your troops in your town. so you have url for explample like this

index.php?target=1234&x=10&y=12 //x + y so you can check if the target is right

then you display a form where you can select the amount of your troops and action

and post array
$_POST => array(
'action'=>'attack',//attack or assist or other actions
'troops'=>array(
'spear'=>10 //Unit Name => Amount
'axe'=>1
)

);

so you need a table like this

id | group_name | unit_name | amount

group_name could be username+number

then you have your troop_movement table

id | sender | target | group_name | arrive_time | action

after selecting your troops you add your troops in a group, then you add te group in troop_movement table.

but this wasnt tested, its just an idea.

Greetz BlackScorp

sorry for my bad english:D

[Jesterhead]

Thank you scorp but that was kind of the idea I stated first in my main post

[BlackScorp]

oh i couldnt see that you group up your troops.

ID | Location | Unit 1->x

there isnt a group name. and Unitname + amount you cannot put into one cell.

THe Problem with your Own Troops, you could clear with a table unit_in_town

id | town_id | group_name |

so you use your group table for every troop managment. you use the groups for movement and waiting in a town.

the Problem with that group table is, that isnt performant. if you make a group table lile this

id | unit1 | unit2 | unit3 | unit4

so id of the group and amount of each unit. that would be more performant but it isnt scalable. if you add somedays a new unit , you need to add ne column..

[Jesterhead]

With "ID | Location | Unit 1->x" I meant "ID | Location | Unit 1 | Unit 2 | Unit 3 | ..."

Still 2 options left: What would you prefer as player?
- Automatically make groups, you just send x units (less work for the player, harder to code, more database usage)
- Manually make groups, you can send only groups (more work for the player, easier to code, less database usage)

[BlackScorp]

- Automatically make groups, you just send x units (less work for the player, harder to code, more database usage)

this, as player i want to have easy gameplay to have fun and come back often ingame..

[Jesterhead]

Well yes, I think it is doable as long as I delete all the empty records...
Any other opinions?

[Chris]

Automated (manual could be better if you had some game design idea how to use these properly, but since you don't have it then the choice is obvious).
Also I think you are agonizing over it a bit too much. It is something that can be changed easily later if needed. Sure, inventing a proper database schema is important, but not wasting too much time on thinking is important too

[133794m3r]

Well yes, I think it is doable as long as I delete all the empty records...
Any other opinions?

if you're sending a 'group' to another location. It's just a location data. And you could just have them as part of the players troops(as the other thing i stated was), you could have their 'area' or well add a in_army column. Then just change that value automatically and do x groups. Or just make them always grouped in '10' or some similar manner. And make those groups that you send that are in a greater amount than ten a multiple of it. It allows the players to send groups automatically and makes sending a 'group' of troops to a location much easier. Sure you don't get the exact, finesse of sending out a bunch of single troups, but you could just as easily make it five troops per group.

Also the troop's id, could just be the group's id. And the player would be sending those 'troops' to another player by just changing the 'area' id. The player wouldn't see them, since they're not in their 'area' or well in their 'army' or whatever. And the code when selecting their troops could be.

Code: [Select]

SELECT * FROM troops WHERE in_army=players_armies_ids;

and that'd be all that was required for them to 'go away' from the player. If you're wanting to have the players be able to have 'multiple' armies, then you could just create a table that holds armies, they are a single one row per army and look like this.

Code: [Select]

troops_armies(
army_id int unsigned not null,
owner_id int unsigned not null,
primary key(army_id),
index(owner_id)
);

and that's it. You now have the ability to have players send troops to someone to help them, and you can even have them recalled since the troops belong to the player01 but the army is belonged by player02. And as stated, put them in the 'action' as the time before they can be seen. It requires zero extra code for you beyond just saying action_complete<=NOW(); and that's completely it. You now have the ability for troops to move to another area, belong to another player, and be able to be recaleld by the player who created them. All it requires is an additional table. And eight bytes of data, is not insanely high for you to need to be able to solve this.

Also that is for the 'automated' one.

[BlackScorp]

or you make somethink crazy...

troop_movement

id | from_town_id | to_town_id | arrival | army
1 | 1111 | 2222 | 12356678 | {"sword":10,"axe":200,"spear":300}

and using json_encode on army row

[Jesterhead]

Automated (manual could be better if you had some game design idea how to use these properly, but since you don't have it then the choice is obvious).
Also I think you are agonizing over it a bit too much. It is something that can be changed easily later if needed. Sure, inventing a proper database schema is important, but not wasting too much time on thinking is important too

I thought about using these groups/armies/legions (whatever) in a attack/defence system. But it's not planned out at all, I'll just stick to automatic.

On the other hand, I'm just planning stuff right now as I currently have exams (university). Once I'm done I'll start coding