How to structure new MySQL database

Tachyon

Hello, everyone.

I hate being a newbie on a message board with so many pros but here goes.

I'm going to make a new database in MySQL to hold information on short term volunteers for a nonprofit group. I would like to get feedback from people on a good way to structure the tables of the database.

I was going to make 2 tables - one for a volunteer's contact info and one for what skills the volunteer claims to have. The trouble is, there are 52 skills and each skill has 3 pieces of information about it (level of skill, interest in applying that skill and an optional comment). Does that mean I should make 53 tables in the database and tie the tables together with a userid as the primary key? Or would it be better to make 2 tables and have the skill table hold 52 fields? (but then, how does one have 3 pieces of information for each skill?)

I'm just wondering how best to structure this database before I try to make it.

eriksmoen

might be easier with 2 tables...

users
+---------+---------+---------+
| uid     | skid    | etc...  |
+---------+---------+---------+

skills
+---------+---------+---------+
| skid    | SKILL2  | SKILL3  |
+---------+---------+---------+

SELECT  * FROM users JOIN skills ON users.skid = skills.skid;   (SQL99)

OR

SELECT * FROM users,skills WHERE users.skid = skills.skid;      (SQL89)

you should then be able to view skills based on the user... or so i think... i dunno... it's late 😛

hope it helps

mtimdog

You just really be using 3 tables. The Users table to hold all users informations (except their skills). The Skills table to hold all the info about the skills (3 fields and a 4th for the id).
And an associative table to hold the ids of the User and the Skill.

Then when trying to find the skills of a user, query similar to the last post....

SELECT * FROM userskills JOIN skills ON userskills.skid = skills.skid where userskills.id = 'idofuser';