Many To Many Relationships Stress!!!

[CHawk]

Dear All,

Can someone please help me to resolve my question about the basic table structure in my database. I am creating a database which should contain the following:

Contacts Table

Students Table

Lessons Table

I am confused when it comes to the following point:

The students are couples. I have to be able to have a man and a lady from the contact list, but I cannot seem to find a way to bring two different 'Contacts' and their respective contact information - i.e. telephone numbers, emails, etc. into my 'Students' layout. I am assuming that this is some form of 'Many to Many' relationship.

More than one contact exists in the 'Students' couple

More than one 'Student' exists in the 'Contacts'

Can someone please point me in the right direction?!?!?!

[Ocean West]

why not use a table called "family" it is a table of just Families that has two contacts (couples)

then relate the family table to the students. The Miller's record in family is related to Bob and Betty (two records in contacts) the Family record "Millers" are related to the students table.

Every family would have 1 record and there will be 1 or more record for every contact in the case of couples.

[Ocean West]

Or more normalized you would have a join table between contact and students a record being contact/student this way you can have more than one "couple" who may not have the same "family name",

STUDENT ------CONTACT

when in the Student file looking thru the join file at the contacts you could have a portal

that displays all contact data for the student.

[CHawk]

I am not sure about the fields to include:

The 'Contacts' have all of the regular contact fields.

The 'Students' Layout should have a portal for the man, and a portal for the lady contact - thus pulling each person's contact info into the 'Students' layout.

What would this 'Families' Table contain?

[CHawk]

I am going to try this question again.

My contacts can belong to more than one 'Student' couple

e.g Fred Smith can be part of Fred Smith & Paula Jones and part of Fed Smith & Angela Hughs

My 'Students' normally contain more than one contact.

How does the link work?? What am I missing???

[The Shadow]

If its always just two, you could have maleContactId, femaleContactId in your student table and relate each of them to the contact table separately.

You add an additional occurrence of the table "Contact" to your graph to allow you to draw a relational line between it. You could call the two occurrences "Male Contacts" and "Female Contacts", but there would only be a single Contact base table they were both present in.

[CHawk]

Can I just clarify that - sorry, but I am new to this:

In 'Contacts' there is a _kp_contact_id

In 'Students' there are two foreign keys - a '_kf_malecontact_id' and '_kf_femalecontact_id'??

I understand the second occurrence of the 'Contacts' table...

[Fenton]

This is a standard 2 tables with join table construction. I created the "2 records" limitation by just showing 2 rows of the portal in Registration.

Though you might want to it the other way, with MaleID and FemaleID.

StudentContacts.fp7.zip

[CHawk]

Thank you all for your help. After several hours, I have finally got a working model to build on! All my gratitude to the helping saviours. I will probably be back!!!