relationship of staff students and projects

[Glenda]

Hi

I have staff member, students, and external members, any can be on a project. students have staff members as supervisors and the reverse. I have tried to keep the 3 tables (staff students external) seperate but this makes it difficult to use in projects then as you need 3 different field when you only want one that can look up 3 different tables. I then tried to create a single tabel called people and linked this to all three other tables but seemed to have problems then when looking a what staff supervised what students. Can anybody see and way around this? Besides projects I have grants, publications, travel etc.

hoping for help

Glenda

[Glenda]

I can try and get a link o the database if anybody can help

Glenda

[Glenda]

I have attached a pdf of the database if anybody can help

Glenda

faculty.pdf

[comment]

Your pdf is both unreadable and a bit too complex to take in at a glance.

I'd think a single People table, with a field for SupervisorID. Assuming each person can have at most one supervisor, you'd enter the supervisor's SerialID into the SupervisorID field, and create a selfjoin to another TO of People from SerialID to SupervisorID.

This will enable you to see all people supervised by X from one side, and Y's supervisor fom the other.

[Glenda]

Sorry about the pdf. I think what I am trying to do is a standard relational schema in filemaker when it has to be somehting slightly different.

I know what you mean about the supervisor within the people field but that will not work as a person can supervise many students the same as a student has more then one supervisor. I will continue to think on htis and see if I can't do and easy example to begin.

GLenda

[comment]

Then you will need a join table of supervising Assignments, but the principle is the same - see attached.

Supervisors.fp7.zip