MySQL design help

Report · Jan 27, 2007

I have a situation where the relationship between to tables (team,skills) is
many to many

members of the team have many different skills
skills can be performed by many different team members
not all skills can be performed by each team member

On individual php web page I will display a skill and the team members with
that skill

I was thinking to build two tables:

skills
skill_id (int)(primary_key)
skill_name (varchar)
skill_description (varchar)

team
team_id (int)(primary_key)
member_name (varchar)
member_bio (varchar)
skill_1 (boolean)
skill_2 (boolean)
skill_3 (boolean)
skill_4 (boolean)
skill_5 (boolean)
skill_6 (boolean)
skill_7 (boolean)
skill_8 (boolean)
skill_9 (boolean)
skill_10 (boolean)

Is there a better way to do this?
On the same php page is it possible to display data from two tables that do
not share a foreign_key?

Jeff

Report · Jan 27, 2007

Jeff wrote:
> I have a situation where the relationship between to tables (team,skills) is
> many to many
>
> members of the team have many different skills
> skills can be performed by many different team members
> not all skills can be performed by each team member

You need a lookup table to link the two:

skills
skill_id (int)(primary_key)
skill_name (varchar)
skill_description (varchar)

team
team_id (int)(primary_key)
member_name (varchar)
member_bio (varchar)

indiv_skills
skill_id
team_id
primary key (skill_id, team_id)

By making the skill_id and team_id a composite primary key, you ensure
that each entry is unique.

SELECT skill_name, skill_description, member_name, member_bio
FROM skills, team, indiv_skills
WHERE skills.skill_id = indiv_skills.skill_id
AND team.team_id = indiv_skills.team_id

>
> On individual php web page I will display a skill and the team members with
> that skill
>
> I was thinking to build two tables:
>
> skills
> skill_id (int)(primary_key)
> skill_name (varchar)
> skill_description (varchar)
>
> team
> team_id (int)(primary_key)
> member_name (varchar)
> member_bio (varchar)
> skill_1 (boolean)
> skill_2 (boolean)
> skill_3 (boolean)
> skill_4 (boolean)
> skill_5 (boolean)
> skill_6 (boolean)
> skill_7 (boolean)
> skill_8 (boolean)
> skill_9 (boolean)
> skill_10 (boolean)
>
> Is there a better way to do this?
> On the same php page is it possible to display data from two tables that do
> not share a foreign_key?
>
> Jeff
>
>

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 27, 2007

David, thank you for your guidance.
I see how the lookup table will tie the other two other tables together.
But, in the team table, how do you tell which skills each team member posses
without the skill_1, skill_2, etc... rows?

I think I might be out of my league with this one, maybe I need to buy
another one of your books.

Jeff

"David Powers" <david@example.com> wrote in message
news:epgb61$bqk$1@forums.macromedia.com...
> Jeff wrote:
>> I have a situation where the relationship between to tables (team,skills)
>> is many to many
>>
>> members of the team have many different skills
>> skills can be performed by many different team members
>> not all skills can be performed by each team member
>
> You need a lookup table to link the two:
>
> skills
> skill_id (int)(primary_key)
> skill_name (varchar)
> skill_description (varchar)
>
> team
> team_id (int)(primary_key)
> member_name (varchar)
> member_bio (varchar)
>
> indiv_skills
> skill_id
> team_id
> primary key (skill_id, team_id)
>
> By making the skill_id and team_id a composite primary key, you ensure
> that each entry is unique.
>
> SELECT skill_name, skill_description, member_name, member_bio
> FROM skills, team, indiv_skills
> WHERE skills.skill_id = indiv_skills.skill_id
> AND team.team_id = indiv_skills.team_id
>
>>
>> On individual php web page I will display a skill and the team members
>> with that skill
>>
>> I was thinking to build two tables:
>>
>> skills
>> skill_id (int)(primary_key)
>> skill_name (varchar)
>> skill_description (varchar)
>>
>> team
>> team_id (int)(primary_key)
>> member_name (varchar)
>> member_bio (varchar)
>> skill_1 (boolean)
>> skill_2 (boolean)
>> skill_3 (boolean)
>> skill_4 (boolean)
>> skill_5 (boolean)
>> skill_6 (boolean)
>> skill_7 (boolean)
>> skill_8 (boolean)
>> skill_9 (boolean)
>> skill_10 (boolean)
>>
>> Is there a better way to do this?
>> On the same php page is it possible to display data from two tables that
>> do not share a foreign_key?
>>
>> Jeff
>
>
> --
> David Powers, Adobe Community Expert
> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
> Author, "PHP Solutions" (friends of ED)
> http://foundationphp.com/

Report · Jan 27, 2007

Jeff wrote:
> I think I might be out of my league with this one, maybe I need to buy
> another one of your books.

This principle is one that I have addressed in most of my books,
although not "Foundation PHP for Dreamweaver 8". Let's say you have
three tables like this:

skills
skills_id 1 HTML
skills_id 2 PHP
skills_id 3 JavaScript

team
team_id 1 Tom
team_id 2 Dick
team_id 3 Harry

indiv_skills
skills_id team_id
1 1
1 2
1 3
2 1
2 3
3 3

Tom (team_id 1), Dick (team_id 2) & Harry (team_id 3) all have HTML
(skills_id 1) as a skill.

Tom (team_id 1) & Harry (team_id 3) have PHP (skills_id 2) as a skill.

Only Harry (team_id 3) has JavaScript (skills_id 3) as a skill.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 27, 2007

Thank you again for your help, I read PHP for Dreamweaver 8 and it gave me a
leg up, and I did a few simple DB projects with simple tables or multiple
tables with one to many relationships. Now I've taken on a project that goes
beyond what was covered in that book and discover that I'm lost. Thanks to
you I understand the relationship between the three tables and logic of how
the data is structured.

Unfortunately, short of hand entering the data in MySQL I haven't a clue as
to how to use on-line forms to add, edit or delete data that applies
simultaneously to the three tables.

Could you give me a recommendation? Is there a book that will get me there?
If not, I may just sub out this part of the project?

Jeff

"Jeff" <jeffs@NoSpamLamSam.com> wrote in message
news:epgd3m$drg$1@forums.macromedia.com...
> David, thank you for your guidance.
> I see how the lookup table will tie the other two other tables together.
> But, in the team table, how do you tell which skills each team member
> posses without the skill_1, skill_2, etc... rows?
>
> I think I might be out of my league with this one, maybe I need to buy
> another one of your books.
>
> Jeff
>
>
> "David Powers" <david@example.com> wrote in message
> news:epgb61$bqk$1@forums.macromedia.com...
>> Jeff wrote:
>>> I have a situation where the relationship between to tables
>>> (team,skills) is many to many
>>>
>>> members of the team have many different skills
>>> skills can be performed by many different team members
>>> not all skills can be performed by each team member
>>
>> You need a lookup table to link the two:
>>
>> skills
>> skill_id (int)(primary_key)
>> skill_name (varchar)
>> skill_description (varchar)
>>
>> team
>> team_id (int)(primary_key)
>> member_name (varchar)
>> member_bio (varchar)
>>
>> indiv_skills
>> skill_id
>> team_id
>> primary key (skill_id, team_id)
>>
>> By making the skill_id and team_id a composite primary key, you ensure
>> that each entry is unique.
>>
>> SELECT skill_name, skill_description, member_name, member_bio
>> FROM skills, team, indiv_skills
>> WHERE skills.skill_id = indiv_skills.skill_id
>> AND team.team_id = indiv_skills.team_id
>>
>>>
>>> On individual php web page I will display a skill and the team members
>>> with that skill
>>>
>>> I was thinking to build two tables:
>>>
>>> skills
>>> skill_id (int)(primary_key)
>>> skill_name (varchar)
>>> skill_description (varchar)
>>>
>>> team
>>> team_id (int)(primary_key)
>>> member_name (varchar)
>>> member_bio (varchar)
>>> skill_1 (boolean)
>>> skill_2 (boolean)
>>> skill_3 (boolean)
>>> skill_4 (boolean)
>>> skill_5 (boolean)
>>> skill_6 (boolean)
>>> skill_7 (boolean)
>>> skill_8 (boolean)
>>> skill_9 (boolean)
>>> skill_10 (boolean)
>>>
>>> Is there a better way to do this?
>>> On the same php page is it possible to display data from two tables that
>>> do not share a foreign_key?
>>>
>>> Jeff
>>
>>
>> --
>> David Powers, Adobe Community Expert
>> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
>> Author, "PHP Solutions" (friends of ED)
>> http://foundationphp.com/
>
>

Report · Jan 28, 2007

Jeff wrote:
> Unfortunately, short of hand entering the data in MySQL I haven't a clue as
> to how to use on-line forms to add, edit or delete data that applies
> simultaneously to the three tables.
>
> Could you give me a recommendation? Is there a book that will get me there?
> If not, I may just sub out this part of the project?

It's actually quite simple. I show how to hand-code it in Chapter 14 of
"PHP Solutions", but the principle is very similar to the way the
author_id foreign key is handled in "Foundation PHP for Dreamweaver 8".

The insert form for entering a new team member into the database needs a
recordset that uses the following query:

SELECT * FROM skills

Use this to build a series of checkboxes or multiple-choice menu showing
the skills and setting the value to the relevant skill_id. Give the
checkboxes or multiple-choice menu the name skills[] - the square
brackets are necessary to capture the values as an array.

When the form is submitted, an insert record server behavior inserts the
team member's name and bio into the team table.

Before the closing curly brace of the insert record server behavior, you
need to add three further steps, as follows:

1. Query the team table to find the team_id of the person you have just
entered. Capture this value as $team_id.

2. You can now use a foreach loop to build the values to enter into the
indiv_skills table.

// start with an empty array
$values = array();
// loop through skills
foreach ($_POST['skills'] as $skill) {
$values[] = "($team_id, $skill)";
}

// convert $values array to a comma-separated string
$insert_vals = implode(',', $values);

Say the team_id is 58 and skills 5, 7 & 9 have been chosen, this creates
the following string:

(58,5),(58,7),(58,9)

3. This can then be used to insert the values in the indiv_skills table
like this:

$insert_skills = "INSERT INTO indiv_skills (team_id, skill_id)
VALUES $insert_vals";
mysql_query($insert_skills);

When updating a team member, you need to delete all references to that
member in the indiv_skills table, and then re-enter them using the same
foreach loop and insert query along with the update record server behavior.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/