Troop specialty skills

[travo]

Im not quite sure how to go about this... Basically I have Infantry and Mechanics, who can both have certain specialty skills, with a max of 2 specialties per troop.

However Im not quite sure how I would implement this. A column per combination is pretty much out of the question, it would result in way too much data.

All I can think of is serialising an array of what the user has, and inserting that into a row. That way I wont have too many columns, but I will still have a massive amount of data in that column.

Is there a way I might be missing? Or are they my only 2 options?

Thanks

[Nox]

I think it's no different issue from http://community.bbgamezone.net/index.php/topic,2105.0.html
Either have columns Ability1 Ability2 or have them in a special table with unit propreties
Don't really know much about serialised data in db, that's why I don't mention it

[JGadrow]

Specialty skills should have their own table. Then, create a third table to link units to skills. This is what is called a many-to-many relationship. Units can have many skills, skills can belong to many units. Basically, in database creation there are 3 relationships. Each require a number of table to functionally represent that relationship:

One-to-One: 1 table
One-to-Many: 2 tables
Many-to-Many: 3 tables

Hope that helps!

[Nox]

Well, units have stats, stats belong to many units so I think it's the same case and you can treat abilities as properties...at least considering the linking table, their own table depends on how do you plan to hadle them, if you have them completely parametrized in db, than you create a special table, if you'd like to handle them by scripts then I believe you can put them among properties...

[Scion]

Id say its hard to know how best to structure the data without

a) a clear description of what data is to be stored
b) a clear description of the various ways it will be used.

...Srry travo...

actually id go so far as to say having a clear description of what data you want to store and how you intend to use it is 90% of the battle....when you have that then a solution will usually present itself...

[travo]

Ok no probs

All will be integers.

Makari, I dont get why I would need the third table, could you please explain that part?


Scion, basically the player will be able to create a troop. Then they can choose, do they upgrade him to one of the higher units, like pilot, commander, etc. (dw about this part, i worked it out) or he can choose to get the unit to specialise in something (engineering, construction, reconnaisance, etc) up to 2 specialties per unit.

So I somehow need a way to distinguish how many units have no specialties, how many have 1 of this, one of that, just one of this, etc.

They will be used in various ways. etc each engineer will effect the time multiplier (cant remember off the top of my head) for the construction of vehicles. It will also be called in combat. If some infantry have died, the deaths will be randomly spread throughout the specialties.


Thanks

[travo]

Sorry about the double post

Ive been thinking... Would having 1 skill per unit be just as good for players? If anyone here were playing a game, would it make a difference to you whether soldiers can have 1 specialty or 2?

[Scion]

I think i would keep things simpler by only allowing one specialisation....

what makari was suggesting is a join table....they are used to implement a many-to-many relationship in a database design.

for example if you decided to go with just one speciality per troop then you have a one-to-many relationship.....many troops share one speciality...the many side gets a reference to the one side of the relationship so the troop table gets a FK reference to the speciality table...allowing each entry (troop) to indicate what speciality they have.

However if you want to stay with the 2 specialities then you could look at using a many-to-many relationship....each troop can have many specialities, each speciality can be asiigned to many different troops....(Note the DB structure does not limit it to only two you would need to do that programatically) .... you then have a troop table and a speciality table....and you create a join table to link them...for example a table called...trained_in that has a FK reference to the troop table and a FK reference to teh speciality table. For every speciality that a troop gets trained in you add a new row to that table that links that troop to the speciality....If you want to you could add say a date to show when they learnt that skill...or a level to show to what level this troop is trained in that skill....in otherwords the join table can also be used to store additional information about the relationship.

There is another option as well if you want to go with 2 specialisations...use the one-to-many relationship twice. that is have two FK references to the specialisation table...

Note if you want to store things like skill level or learnt date when using the one-to-many relationship then you need to store this information directly on the many side so for exampl youd have troop _specialisation_id to idicate what specialisation, troop_specialisation_date to indicate the date they specialised, and troop_specialisation_level to indicate the skill level.

All of these approaches allow specific information about the specialisation and any bonuses or whatever to be stored in the specialisation table ....

[travo]

I also think I will just go for 1 specialisation. But lets keep the discussion open, Im learbing new things lol, and its kinda interesting.

If I was to use it that way, wouldnt I then need a record for every troop? If so, the data size would become masive, and probably fill up too quick. Unless I am understanding you wrong.

I think I am understanding wrong

Lets say I have my users table with the basic values of how many troops I have for quicker referencing, ie. total number of infantry, each vehicle, etc. Now what?

Could you please explain in terms of 1 user, I think that would be easiest.

Thanks

[jannesiera]

I also think I will just go for 1 specialisation. But lets keep the discussion open, Im learbing new things lol, and its kinda interesting.

I also suggest starting with 1 specialisation. But you could code iot for 2, so you learn to do it proberly and you can add a second specialisation very easily later on.

[Scion]

yes i was suggesting one row per troop... so your troop table would have had (as a bare bones)

troop_id
troop_user_id
troop_specialisation_id

so if you had 1000 users and each had 1000 troops then thats a million rows...not really much to worry about in terms of a DB table....
however i had figured that users would have had in the order of tens of troops.....after all you dont want to have to go through 1000 troops selecting each one specialisation....

perhapes what your thinking about is more sort of just needing to know how many troops have been allocated to each specialisation...ie our army has 1000 pikemen 150 musketeers 50 cannoneers 200 calvalry and 20 scouts. In that case the you are not interested in individual troopers rather collections of troops based on specialisation. So it would be enough to just keep a count of the number of troops with each specialisation.

for example

troop_id
troop_user_id
troop_specialisation_id
troop_count

that way when you commission new troops of that specialisation you increase the count and reduce it when they are lost in battle.

Finally just to keep the performanceists happy....say you notice that whenever you need to report something about a troop you allways have to display the specialisation name and to do that you need to write a querry that joins the troop and the specialisation tables bassed on the troop_specialisation_id FK.... now the specialisation_id column will have a pretty good index on it so finding the specialisation isnt too much of a performance hit, but its still there....so what you might decide to do is bring that specialisation name into the troop table... for example.

troop_id
troop_user_id
troop_specialisation_id
troop_specialisation_name
troop_count

Now we dont need to join to the specialisation table just for display, but we still have the FK if we want to retrieve other information about the specialisation....however we have also bought into a little complication....if we decide to change the specialisation name for some reason (if it was me itd be s spealing mistake getting corrected) then not only do we need to change it in the specialisation tale but also any where in the troop table that it was used.

Some might suggest that you use the troop_specialisation_name as a the FK reference to the specialisation table....id not recomend that approach primarily for the performance that the DB will get on an index on a character field....and why..just to save one extra integer per row....on a table that will be lucky to have 1 million rows...

and now heres my extra for experts section: have a think about how you would be able to assigne troops to an army so that each army can be given a name by the users and the army is made up of any combination of specialised troops.

ps: i hope you realise by now that coding for 1 specialisation or coding for 1+ specialisations means a different db structure, and as jannesiera suggest if you ever want to switch too more than one specialisation then you should choose a db structure that  supports it....however id still be inclined to go for a single specialisation....there may well be other ways to achieve what your wanting with multi specialisation.

[travo]

Yes I have been thinking I will just go for 1 specialisation, it will be alot more simple, and if players want more of a certain specialty, they can train more troops lol.

I wasnt really thinking about having any specific armies, but more things like a tank is useless unless it has been assigned a driver, commander, gunner and loader (this will be really simple though, no half full tanks, just full tanks and empty tanks, can only become full if one of each is available), assigning for example engineers to the garage, etc.

This will just be another col for each assignment around the base, and those troops are not in the usual troop count, they must be resigned from that assignment to be allowed to fight, etc.

Sorry if this isnt understandable, I was knocked out on the weekend and am still slightly concussed, Ive been told by a few people they have no idea what Im on about lol

[Scion]

perhapes one way to look at the assignment process is as a construction/combination process...

in order to build 1 operational tank you need 1 tank, 1 driver, 2 gunners, and 1 officer...so your remove those from your available troops and add a manned tank.

so in terms of the DB you have in your troops table a row for unmanned tanks, a row for drivers, gunners, officers, and manned tanks ....when you decide to man a tank you decrease the counts on tank, driver, etc and bump up manned tank by 1.

that way when an attack or battle occurs all current troops and equipment can be included, Personally im a big believer in making games realistic/sensible....that is if a base has 100 people at it and it comes under attack....you can expect all 100 to partake in the battle to a greater or lesser degree....perhapes the cooks may only be able to inflict minimal damage....and empty tanks none at all....but they should all be able to be damaged during the attack....in fact an empty tank just sitting there should have a considerable negative modifier to defense compared to a manned tank.

The only other point i would raise is to do with micro management....if your game is meant to be a ww2 strategy game then assigning a driver to a tank could probably be considered micromangement and may just distract players from the original intent of the game.....at the very least it would be important to provide tools for automatically managing such things with reasonable defaults. However if the game is called tank comander then perhapes ensuring drivers have been assigned to tanks is relevant.

[travo]

Thats p[retty much what I was thinking about doing for the tanks...

I was also thinking you could assign guys to vehicles so you could attack the way you want. For example if you want to send out a recon mission, you assign a driver and a recon guy to a humvee, while if you want it to transport your troops, you can assign a driver and 7 other guys.

But now that I think about it... The units will only have to be assigned to the vehicles for attacks... So I can take care of that stuff in my attacks table, and it doesnt really matter about assignments, a certain amount will get taken away from the players units and placed in the attacks table, then the alive units will be transferred back...

[KoI]

I personally don't see a problem with having two specializations, and I don't see how that's going to generate a ton of data in your database either.

If players have to manually interact with their troops in order to receive specializations, it's unlikely each player will ever achieve 1,000 troops with 2 specializations each.. it would just take to long to do all that.

The best games in my opinion, allow you to do your primary function within the game very quickly.  Log in, attack someone, view result, buy equipment, attack someone... whatever, in just a minute or two.  There needs to be a good balance of simplicity, functionality, and uniqueness.  If you look at games like My Brute, it doesn't get much more simple than that, and look how viral that became.

If you want to do two stats, I think you're fine doing that.. you don't need to worry about all the possible combinations.  Just assign integers to that troop's "specialization" category, that correlate to id numbers in the specializations table.