Need Database Structure Help
I am creating my first database, and it is a complicated one. I bought the book: MySQL by Paul DuBois, but it does not seem to have much in the 1000 pages about actually designing a database. Using MySQL workbench I created 42 tables that I thought would end up doing what I needed, but when I started to populate with data, I ran into problems, which I believe is due to two problems, my lack of knowledge of workbench, and my lack of knowledge of how keys work. Web searches have not been helpful.
This database is going to be used in creating a character for a game, Dungeons and Dragons. It is just a fun exercise to keep me busy and a working project to learn MySQL. Here is the problem:
Each character has an id (character_id). Each character has many attributes associated with it. Two of these would be feats and skills. Each character gains feats when they increase in level. So a low level character could have 1 feat, and a high level character could have 20 feats. I designed a table to keep track of these like this:
character_id ----- feat_name
1 ------------------ dodge
1 ------------------ endurance
2 ------------------ power attack
...
A character can increase their rank in a skill each level, so for the skill table:
character_id ---- skill_name ---- rank
1 ----------------- swim ------------ 5
1 ----------------- jump ------------ 7
...
Reading online, I read that every table needs a key, so for the above two tables I made character_id and feat_name a key and character_id and skill_name a key as well. This seems to be needed since character_id will not be unique in the table, and I would need the program to look at both columns to be unique. Is this correct?
None of the database examples I can find anywhere repeat a key in multiple tables, but as you can see above character_id is repeated (and this is just a tiny example, it is repeated many more times in my database). In the online examples character_id would of course be in other tables, but in those tables it would not be a key. Is it ok to reuse keys like I have?
Finally if the above two are correct, I am thinking that I might have made the keys primary keys in workbench. Is there a way to separate in workbench which are primary and which are just keys?
As you can see I am sort of stumbling around in the dark at this point. My main problem stems from the fact that all online examples are extremely simple, and I cannot find anything that takes those simple databases and shows you how to structure a more complex one. Any help would be greatly appreciated. Thank you.