Many-to-many relationship issues

[jamesfbt]

INFO

 

Okay, I'm confident someone here can help me out with this problem.

 

Basically I'm creating a database at work to track information that comes from 5 reoccurring weekly meetings and have created the following tables: Meeting, People, Tasks

 

All tables are related and each relationship is many-to-many.

 

One person can have many tasks

One person can attend many meetings

One task can have many people assigned to it

One task can be an initiative of several meetings

One meeting can have many attendees

One meeting can have many tasks

 

I created join tables that look like this

 

|people| -- |meeting|

 

The relationships are setup to allow creation of records in people, meeting and meeting_attendee, and deletion of records in meeting_attendee from both people and meeting.

 

ACTUAL PROBLEM

 

I have a portal in people that shows meetings they belong to and a portal in meetings that shows attendees of a particular meeting.

Those portals reference the meeting_attendee table for their information.

 

Problem is, when I add attendees into the portal in the meeting table, a record is generated in meeting_attendee but no records are generated in the people table. That's what i need to happen.

 

Please help me... This has been killing me for days. Sorry for the long post by the way.

[David Head]

ACTUAL PROBLEM

I have a portal in people that shows meetings they belong to and a portal in meetings that shows attendees of a particular meeting.

Those portals reference the meeting_attendee table for their information.

 

Problem is, when I add attendees into the portal in the meeting table, a record is generated in meeting_attendee but no records are generated in the people table. That's what i need to happen.

Well the good news is that you seem to have the right table structure. smiley-wink You also seem to have set up the portals correctly. smiley-wink

 

The question is how are you adding attendees to a meeting?

 

When you do that, I would not expect records to be 'generated' in the people table. You are adding a link to a people record that should already exist. If a person does not exist, they cannot attend a meeting!

 

Your join table (meeting_attendee) should have foreign keys for meetingID and personID. It is these keys that need to be filled in to create the link from meeting to people and back.

 

Does that make sense?

[jamesfbt]

Yes, that does make sense and I was afraid of that answer. Thanks for the fast response.

 

If that's the case, then here's another related question: In the Meeting table I want to limit the selection of attendees (through the portal to people) to only people records that exist in the People table (via a drop down menu).

 

My problem is that in addition to the drop down menu I have, I can also manually type in a new person. Since many people may use this database that causes a problem. I need to have them create a people record first and not able to type in an entry.