Re: Need Database Structure Help
Thank you very much for your willingness to help examine the db, but just to warn you dungeons and dragons is a complicated game, many different attributes of the character reference/effect other areas. I will start with skills. I am unsure how to use keys, but I am going on what I read online that each table should have at least one key, and it should have as many keys as needed so that all together the keys are unique.
Each character can put a certain number of points (changes for each character type) into their skills each level. Skills are effected by 6 things:
1) the number of points the character has put into the skill (ranks)
2) the value of the character's attribute the skill relies on - for example Jump is a strength skill, so if a character's strength score is +2, 2 will be added to the skill total
3) The character's race (elf, dwarf...) can effect certain skills
4) If the character has 5 or more ranks in some skills it can give a +2 bonus to certain other skills, for example if the character has 5 or more ranks in Tumble, they will get +2 to Balance
5) items the character uses can give bonuses to skills
6) Some skills are effected by the armor the character wears, the heavier the worse.
Finally some skills are considered "class" skills for a character based on their class. For these skills 1 point = 1 rank
Other skills are "cross-class" 2 points = 1 rank
And some skills are completely off limits.
So for a fighter, Jump is a class skill, Diplomacy is a cross class skill, and Use Magic Device is off limits.
I broke skills up into 6 tables:
-- tb_Skills
skill_name [key]
attribute
armor_check
This takes care of the attribute (#2) and armor (#6), armor check is bit (in the game it is yes or no). This is the most straight forward table and contains the base information on the skills.
-- tb_Skill_Syn
base_skill [key]
bonus_skill [key]
This takes care of #4. The code will match the base skill to the skills the character has, and if the character has the skill with 5 or more ranks in it, it will add 2 to the bonus skill (if the character has it). Both are keys since one skill can effect several bonus skills.
-- tb_Skill_Class
class [key]
skill_name [key]
class_cross
This takes care of the cross class issue. There are a base set of skills (I think 20). They will be contained in the table tb_Skills. There are many classes (each book they publish has more in it). Each class has access to different skills. So this table would list the class, the skill, and if the skill is cross class or not (once again bit). So: Fighter, Jump, 0. I dont like this one too much because that means 20 skills listed seperatly for fighter, then 20 skills listed for wizard... I have to be ready for an ever increasing number of classes though as more are created in the future. Class and skill_name are keys since each skill will appear exactly once for each class, and each class will have many skills.
-- tb_skill_equip
skill_name [key]
equip_name [key]
bonus
This takes care of #5, this would list the item, the skill it effected, and the bonus it gave. Skill_name and equip_name have to be keys since one equipment can effect several skills.
-- tb_skill-race
skill_name [key]
race [key]
bonus
This takes care of #3. Each race effects different skills, at different amounts. For example Elves get +2 to listen. skill_name and race are keys since each race can have multiple skills effected.
Finally:
-- tb_character_skills
character_id [key]
skill_name [key]
rank
Each character has a unique id, so this table keeps track of what skills each character has and how many ranks in each skill. Character_id and skill_name are keys since a character can have multiple skills.
I hope this makes sense. As I said the rules are complicated. Did I set these tables up correctly? Is there a more efficient way to represent the data? Am I using keys correctly (I suspect not, but I am not sure)?
Thank you.