ethics in Relationships

[rivet]

Part of my solution has a contacts portion. There are two distinct type of contacts that MUST be entered and managed in separate layouts (eq Students, and Teachers). The common fields are feed into shared tables. (contact, locations, numbers) and keyed with either a Student_ID or Teacher_ID.

TABLES:

Students

Teachers

Contact

Location

Numbers

Teams

RELATIONSHIPS:

Student -> ContactInfo -> LocationInfo -> NumberInfo

Teacher -> ContactInfo -> LocationInfo -> NumberInfo

MY CHALLENGE:

The DB now has a table called TEAMS which will pull information from the contact table, since teacher student status is irrelevant. But the location and number records are not related to the contact record, they are related to the student and teacher records. (note contact to teams is a one to many)

Currently I have added a contact_ID to both the location and number tables that will check the Student_ID and Teacher_ID for a value and then pull the contact_ID from the appropriate Student/Teacher to contact relationship. This is starting to feel messy like FMP6 and I have a feeling that I am not taking advantage of FMP7

Please let me know if you can see a better for me to handle my relationships.

[The Shadow]

So, when you say MUST be separate, do you mean required by law?

That split is going to make the relationships difficult.

You could still have two layouts even if they shared the same table, and/or use access privileges to limit the view privileges for certain records.