MySQL Forums :: Newbie :: Need Database Structure Help

Advanced Search

Need Database Structure Help
Posted by: Alex Bursch ()
Date: October 09, 2010 11:43AM

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.

Options: ReplyQuote

Subject Written By Posted
Need Database Structure Help Alex Bursch 10/09/2010 11:43AM
Re: Need Database Structure Help laptop alias 10/09/2010 12:11PM
Re: Need Database Structure Help Alex Bursch 10/09/2010 09:08PM
Re: Need Database Structure Help laptop alias 10/10/2010 03:20AM
Re: Need Database Structure Help Alex Bursch 10/19/2010 06:51PM
Re: Need Database Structure Help Chad Bourque 10/19/2010 08:03PM
Re: Need Database Structure Help Rick James 10/20/2010 09:16AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.