2. PHPRunner General questions
  3. Many to many relationships
This topic is locked

Many to many relationships

 		7/3/2008 5:28:25 PM
PHPRunner General questions
T
 thesofa author

Hi

I have to write a new database where people and their skills are linked. Each person may have many skills, each skill may belong to many people. A classic many to many relationship.

So, I have 3 tables, members, skills, and smf_memskill

I want the main table to be Members, but I would like each member to have links to many skills.

The smf_memskill table has the following field structure

-- Table structure for smf_memskill

-- ----------------------------

CREATE TABLE `smf_memskill` (

`ID_MEMSKILL` mediumint(8) NOT NULL auto_increment,

`ID_MEMBER` mediumint(8) default NULL,

`ID_SKILL` mediumint(8) default NULL,

PRIMARY KEY (`ID_MEMSKILL`),

KEY `ID_MEMBERS` (`ID_MEMBER`),

KEY `ID_SKILL` (`ID_SKILL`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 5120 kB';



So this table has a unique key and each record links one member and one skill.

How do I set this up in PHPR to give me a table where I can look at the list of members and by hovering over the link, see a brief list of their skills, but also search for a skill and see all the members with that skill.

Am I getting into the realms of custom code here, or is there a template ideally suited to this application?

TIA
A
 		alang 7/6/2008

In one case set up Members as a master table (smf_memskill as the detail table) and in the other case setup Skills as the master table (again with smf_memskill as the detail table). In each case you will need to add the appropriate table to your SQL and link to the smf_memskill table to get useful fields for your list view.
T
 		thesofa author 8/6/2008

Hi

I have returned to this one after a while of thinking, it has now developed in our needs quite a bit.

here is what we want.

Initial view of a list of skills available to use, with a link field as from a master/detail relationship, leading to a link table of members/skills.

again a list showing just the link to the members who have the skills.

On clicking on the link, a list of links to member's addresses and contact details, ie, home address, work address, home phone, work phone, (personal details such as email, IM contact and mobile numbers are to be held with the member's detail rather than with the address, this seemed to be best after normalisation)

on a final click, the contact details will be shown.

so the route to the information is

  1. search for skill
  2. click links to people
  3. click on perrson's name
  4. click links to addresses
  5. click address for details


so address is detail to memadd

memadd is detail to members

members is detail to memskill

memskill is detail to skill.

now it would seem that for a table to become a detail table to a master table, there must be a foreign key in the detail table. This stops me from entering different skills for the three brothers in one house, as well as stopping me from storing the same work address for 3 colleagues with differing skills

This would mean that only one member could have a given address as the address has to have a foreign key pointing to the member for the master/detail system to work.
Is there a way to get the links on the skills list page to allow me to link to the members/skills link table, and thus to the members table.

I can set up a query that shows all the members names and other details for a given skill, but I rather like the links for the master/detail table system, it makes it simpler for users to follow, it allows showing of a preview possible and it keeps the master details in view as well.
Please help as I hate to admit defeat.....

as we all do I hope

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=31883&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />