New AA Table Diagram / Schema.

[Thristam]

I created this to help "fix" the AA tables in the DB. If yuo have suggestions on how this could be better, chime in please. Keep in mind i wanted to make it customizable, so its not as efficent as it could be.

Also, i may not have a full grasp of where certian skill data is stored. Anyone who knows better is welcome to explain it. Thanks.

Issue: A significant number of AAs are not implemented. Almost none of the 'activateable' AAs are not working right now. A good system needs to be developed to support them. This involved analyzing all of the AAs currently and determining what database structure needs to be implemented to support them.


Problem: To create a DB structure that will allow for the storage and tracking of all AAs currently in game.

Suggested solution: Create a multilayered db that allows all AA's to be implemented, but gives full customization of new AAs.

Tables Diagram is here




AA_Character: This is the table which identifies the character and gives general information.

Id - Used to identify and relate from aa_character to aa_information tables.

general_points - Used to track how many general points a character currently has. 0 means they have none.

archtype_points - Used to track how many archtype points a character currently has. 0 means they have none.

class_points - used to track how many class points a character currently has. 0 means they have none.

min_general_points - used to set a threshold on how many points a character must have in order to unlock their archtype AA's. Having this Column here allows for making this threshold variable. 0 means they can buy archtype AAs without a need to have a default amount of general points.

min_archtype_points - used to set a threshold on how many points a character must have in order to unlock their archtype AA's. Having this Column here allows for making this threshold variable. 0 means they can buy class AAs without a need to have a default amount of archtype points.



AA_Information: This links the customer general information to the actual AAs. I went with this cos it was the most customizable.

id - Used to identify and relate from aa_information to aa_data.

rank1 to rank5 - used to set ranking of aa's. An id number is assigned here from the aa_data id which designates abilities of this rank. This allows for custom ranks to aa's of any order.

rank1_enabled to rank5_enabled: Used to set wether or not a rank is active for this character. This allows to enable ranks out of order or even for free. 0 means this rank is not active. (This column could be optional. Setting the default of the Rank1 to rank5 Column to 0 or null could just as easily work. Just depends on the logic you wanna use.)



AA_Data: All data pertaining to the abilities themself. Yes, there will be some duplicate data, but i found this to be the best customizable option.

Id - Used to identify and relate from aa_data to aa_information.

aa_name - Contains the name of the ability.

aa_class - Contains which classes can use the ability. 65535 means all classes.

spell_id - The ID number (if any) for the spell being used for this AA. Things like Bestow Divine Aura, Call To Corpse, and Dire Charm would be examples of spells being cast. I would make the suggestion of using the Spells table to add said spells (unless they have to be a flat file or somewhere else). 0 would mean no spell. This would allow for an AA to have a spell in its ability along with other powers (if wanted).

spell_duration - This is the column which contains the duration in time for (spell_id). Im assuming this would be a percentage number (without %) to show the increased duration. IE: 20 - would be 20 percent increase in duration. 0 would mean no change. -10 would be 10% less duration. This allows you to use a standard or custom spell(s), then increase (or decrease) the duration of said spell.

spell_redux - This is the column which contains the reduction in mana for (spell_id). Im assuming this would be a percentage number (without %) to show the reduction. IE: 20 - would be 20 percent reduction. 0 would mean no change. This allows you to use a standard or custom spell(s), then decrease (or increase) the cost of the spell.

cast_time - This is the column which contains the reduction in cast time for (spell_id). This would work on the percentage reduction as the above spell_redux. 0 would mean no change. This allows you to use a standard or custom spell(s), then decrease (or increase) the cast time of said spell.

cast_speed - This is the column which contains the reduction in cast speed for (spell_id). This would work on the percentage reduction as the above spell_redux. 0 would mean no change. This allows you to use a standard or custom spell(s), then decrease (or increase) the cast speed of said spell.

stat_id - This is the identifier for the stats for this player. This may be not needed at all if you can pull the identifier directly from the player profile. Otherwise, you can use this to identify the stat being changed:
Strength
Stamina
Agility
Dexterity
Wisdom
Intelligence
Charisma
Fire Protection
Cold Protection
Magic Protection
Poision Protection
Disease Protection

stat_increase - This column is used to show by how much the effected stat in stat_id is being increased by. This is a flat number so, if a toon had a base 50 in strength and gained a rank in Innate Strength this column would have a 2 for this id, which would add 2 to strength (or -2 to take some away) for this rank. 0 would mean no change. This allows for custom stat AA's.

nonspell_action_ID - This ID is set for Actions that are not defined by class. examples would be: Combat Agility, Finishing blow, 2 Hand Bash, Abmidexterity etc. Again, im not sure where these are stored in the EMU, so i left this as a int to be defined.

nonspell_duration - Contains the duration in time for nonspell_action. Im assuming this would be a percentage number (without %) to show the increased duration. IE: 20 - would be 20 percent increase in duration. 0 would mean no change. -10 would be a 10 percent reduction. This allows you to use a standard or custom nonspell action.

nonspell_redux - Contains the reduction in stamina for nonspell actions. Im assuming this would be a percentage number (without %) to show the reduction. IE: 20 would be 20 percent reduction. 0 would mean no change. -10 would be a 10 percent increase. This allows you to use a standard or custom nonspell action, then decrease (or increase) the cost of the nonspell_action.

nonspell_Stamina - Amount of stamina it would take to make a certian nonspell_action work. Some nonspell actions use stamina instead of mana to be preformed.

reuse_time - Contains the reduction in use time for both spell and nonspell_actions.. This would work on the percentage reduction as the above redux's. 0 would mean no change. This allows you to use a standard or custom spell or nonspell actions, then decrease (or increase) the reuse time of said actions In Seconds.

aa_cost - Associated cost of the AA_ability. This allows for custom aa costs or even free AA abilities. 0 means no cost.

aa_type - This bit details the 2 types of AA abilities:
0 = passive
1 = activated

level_restriction - This is where you can set level restrictions based on the ability. 0 for no restriction.

aa_restriction - The AA_data ID the Character must have In AA_information in order to purchase this ability. the ID cooresponds with the ID placed in the Rank1 to 5 Ids. 0 means no aa_restriction.

aa_Discriptor - Contains the description of the Ability.(optional)