Third file related on two others

[jlmc]

Hi, and thanks in advance for your help. I can't seem to get my brain around this relationship .

I have a file [Contacts]. Each Contact could have multiple Children for which I have set up a file [Children] and a relationship that works - no problem.

Now, a contact could be a Student. Also, a Child could be a Student. (Each record in these files has a "yes/no" field for Student.)

I need a third file for Students where a record will be created automatically from the Contact or Child file when the "Yes" box is checked for someone who is a Student. This file will also hold other information such as Emergency Contacts, Medical problems, etc. It will also be related to classes, events, etc.

I may just be stuck looking at this from one angle and not be able to see around the corner - hope you can help.

[comment]

IMHO you should have one file for all the people. If there are many additional fields for students that you don't want included in the Contacts file, then create a second file - StudentInfo - just for the additional information.

Contacts file fields:

- PersonID

- Name

- ParentID

- isStudent

- etc..

StudentInfo file fields:

- StudentID

- Emergency Contacts

- etc..

Relationships:

Parent (selfjoin, ParentID = PersonID)

Children (selfjoin, PersonID = ParentID, allow creation)

StudentInfo (PersonID = StudentID, allow creation)

A portal to Children will allow auto-creation of new Child record/s from the Parent record.

A portal to StudentInfo will allow auto-creation of new StudentInfo record from the Person record.

[Ender]

Hmm, I don't really like the structure comment has suggested. If you're dealing with Students, then there's probably a whole lot more Student data than Contact data, so it makes more sense to use something closer to your current structure.

To answer your question about where to store Emergency Contacts and Medical problems, we first must understand how many records of each of those are expected per Student, and if you need to store the data from previous years.

In a typical enrollment system, you have a table each for Family, Student, Enrollment, Section, and Course. If there are multiple emergency contacts, then they can be put into a separate table and related to the Student. If you'd like to keep past medical records intact, then you could put that into into Enrollment, as there will be one Enrollment record per year (or whatever your enrollment period is.)

This idea of keeping of historical data is important to understand when designing a Student Data Management System (if this is what you're doing.)

See the attached ER diagram for my solution to this design problem. Although Families can be handled in a variety of ways, depending on what's important about your family data, I use a Family table that can have many Students and many Other Family Members. For any given Student, there are one or two Parent/Guardians, chosen from the list of Other Family Members.

I'm not sure if this Enrollment system is the main part of your solution, or the Contact manager is. I think if it's the Enrollment system, then you should start there.

[comment]

I agree. It really depends on what's the primary goal of the solution.

[Søren Dyhr]

I use a Family table that can have many Students and many Other Family Members. For any given Student, there are one or two Parent/Guardians, chosen from the list of Other Family Members.

Good point Indeed ...I've admittedly been tempted by the same recursive'ish structure as Comment - but giving it a stab as a template caused me into deep troubles by making way too many calcfields to control the scope of the relations.

This is indeed a "Just becasue we can ... should we??" question!

--sd

[jlmc]

Thanks all of you for your help.

Yes, it really does depend on the purpose of the database, and it is a bit complex. I am attaching a description. Please note, I am working if FMP 6. I have most of it working, but need to fix a few things, primary is the Student file.

Thanks again

[jlmc]

Sorry, did not attach the description (cant find a way to do that).

IDTflowchart.zip