Join tables?

[filemakerlearner]

Hello,

I'm new to Filemaker, although I've programmed in other languages before. I am designing some tables for a database, and was wondering if I could sanity check the way I'm going about it with the forum.

I have several tables in my database -- one that contains student records; one that contains professor records; etc. Each of these types of people needs to have several addresses associated with their record. So I've also created an addresses table.

My question is regarding how to link the addresses to the people. Since the student and professor tables each have their own serial numbers (and I guess there might be overlap between the numbers since it's different tables), I can't just have a "person ID" field in the addresses table to link to those tables. And since there are a variable number of addresses I can't put an address ID in the student/professor records. So, I was thinking I would have to make a "student to address" table that just has the student ID and the address ID; and then a "professor to address" table that just has the professor id and the address id.

It seems a little clunky, is there something I am missing?

Thanks so much.

[LaRetta]

Each of these types of people needs to have several addresses associated with their record. So I've also created an addresses table.

I would suggest that people be in the same table and a field called Type identify them as Professor or Student. Actually, I would think that the Type field would even be possible multiline with both professor and student in it (if a professor decides to take a class as well). If you ever get this dual possibility, having them in separate tables will require duplicating the person's information in both tables and that defeats good design, in my opinion.

[filemakerlearner]

Interesting. Well, the thing is, I need to track different information about the professors than the students -- so I need different fields within the professor/student records. Also, for the purpose that I'm creating, they're not going to overlap (given certain restrictions on the project).

I suppose I could put a type field in the addresses table and then just have a single "person ID" field in the addresses table. But it still feels a little clunky to me having to hardcode types into the address table...

[LaRetta]

I didn't really finish my thought ... if the same table is used for all people, then the Address table would hold the PersonID so that multiple addresses can belong to same person. But also, multiple people could live at same address, right? You might consider a join file.

As for the Person's Type being multiline, you would actually define someone as a Student by their attendance record in the Classes table.

[LaRetta]

The fact that professors have different information than Students isn't an issue. If there are only a handful of fields which are different, keep them in the same table and only fill the fields you need. If there are many fields which are different for only the professor (and 'many' is a relative term) then create a Professor table which relates to people which holds these unique fields on a 1:1 (one-to-one) relationship. There is nothing wrong with 1:1 relationships. In general, only split off professor fields if there are more than 15-20 and only split off those fields which will not be used in a filtering relationship in People.

This is not the only design style but I've done it several different ways and I've always regretted not keeping all people together if there is possibility that professor's become students and students become professors and that there is possibility that a User will get a call about Edward Smith and wants to search ONE table - no knowing whether they are a professor or student. Overall, I believe one people table is the way to go.

Edited April 11, 2009 by Guest

[comment]

A simple solution would be to have two foreign key fields in the Adresses table.

Also, depending on how many distinct fields you have for each type, you could consider rolling the two tables together, and leave one group of fields empty.

The most "correct" solution (albeit also the most complex) would be to have one 'supertype' table of People, with 'subtype' tables for Faculty and Students - linked in a one-to-one relationship to People (search the forums for 'supertype' to learn more). In this case, the Addresses would be linked to People only.

[comment]

Sorry, haven't seen your last post.

[filemakerlearner]

The most "correct" solution (albeit also the most complex) would be to have one 'supertype' table of People, with 'subtype' tables for Faculty and Students - linked in a one-to-one relationship to People (search the forums for 'supertype' to learn more). In this case, the Addresses would be linked to People only.

I like this solution, it appeals to my sense of being object oriented. I am getting stuck on one aspect of it though.

So I have a "Person" table now, which I would like to be the supertype and have a "Professor" and "Student" subtype tables. They will link via PersonID, which is a serial number in the Person table and a foreign key for the Professor and Student tables.

I am having trouble setting up the relationship in the graph, though. I have set up the Student/Person relationship and selected "Allow creation of records." But when I go to connect the Professor/Person tables, I get this error: "There cannot be more than one relational path between any two tables in the graph". What is going on?

[comment]

What's going on is that there cannot be more than one relational path between any two TABLE OCCURRENCES in the graph. I don't see your current graph, but you may need more than one occurrence of a table to accomplish what you need (although for a basic Faculty - Contacts - Students, one TO of each should be enough).

Keep in mind that a layout is tied to a TO, so you need to consider carefully which tables get additional occurrences and where.

[filemakerlearner]

OK. I've been reading on TOs and have gotten further along thanks to that.

My tables and relationships are now set up. Now, I am trying to do some things on a layout and having some trouble figuring out what tools to use. I was wondering if anyone could suggest some to me.

I have the user selecting a student name from a dropdown list of students. Each student has several course plans, of which only one has an "active" status (the course plan table has a status field). Once the student is selected, I want other fields to automatically populate with data from other fields in the course plan table. I would also like to do some logic -- pop up a warning, for example -- if the student does not have a course plan that is listed as "active." What tools could be helpful to do this?

[comment]

Once the student is selected, a relationship to the Students table should be established. Thus all the data about the selected student becomes available, either directly (place related fields on the local layout), or by a lookup into local field/s.

BTW, a student should be selected by ID, NOT by name.

[filemakerlearner]

OK. I think I am doing that - using the ID, rather than the name.

What tools does Filemaker provide to do the validation -- checking whether the current student's course plan is active -- to then determine what to show in other fields?

[comment]

I am afraid I cannot answer such a broad question. I don't even know what layout you are on, not to mention what your purpose is.

BTW, I think that if a student is allowed to have only one active course plan, then the active course plan is an attribute of the student, not of a plan.

[filemakerlearner]

Sorry, let me try to be more clear, or maybe even give a smaller example that is related.

This is a different database model, but similar. There are clients and staff. I am on the layout for a table that documents instances of service, when a staff person works with a client. In addition, each client has a service plan, which a staff person has helped create (possibly a different staff person, as multiple staff work with the same client).

The user picks a client from a dropdown of available clients. I would then like another field on this layout to immediately populate with the name of the staff who created the service plan (staff and client IDs for the service plan are stored in the service plan). To make it more complicated, each client has multiple service plans (like the course plans above), and each may be created by different staff. However, only one is active. In addition to pulling up the name of the staff person whose ID is in the service plan, I would like to also do some logic double checking that the service plan is active, and popping up a message if it is not.

I know I need to use scripting to do the message and probably the logic at the end. But even without that step, I am unsure how to take the jump from the selected client ID, to then do the logic to the effect (forgive me, I'm thinking in semi-SQL pseudocode)

Select LastNameFirstName from PERSON

Where PERSON.StaffID = ServicePlan.StaffID

and where ServicePlan.ClientID = Current Client ID

This pseudocode is missing the bit about finding the active service plan, but in any event, understanding even a piece of how this works in filemaker would be very helpful!

[comment]

I believe you can do this by a chain of relationships: your service record is related to a client. The client is related to their active plan (remember what I said earlier about this being an attribute of a client). And the active plan is related to the staff who created it. So all you have to do is place the fields from the correct TO of staff on the layout, and they will populate when you select the client.

If you want, you can set field validation to make sure the related client's active plan ID is not empty, or set up a visual cue using conditional formatting based on the same formula.

[filemakerlearner]

Hmm. I am making progress, but still stuck.

I have created a TOG for this layout. The Instance of Service TO links to two TOs: Service Plan (which contains details of the service plan) and Client (which contains the ID of the active service plan). [Note -- I included the ID of the active service plan in the Client record, but I think it makes sense to keep the plan itself in a separate table, since there can be multiple plans over time.]

I have two fields in the layout that I want to autopopulate once the Client ID is selected. (Note: for Client ID, I'm using a value list that maps from Client ID to client name.]

1. I want the active service plan ID to auto-populate. I have this field on my layout set to point to that field in the Client table, however nothing happens when I select the client from the dropdown list. Maybe I have the field settings wrong? It is an edit box -- I am not sure how to make it a non-editable thing that just shows a value. I've checked and I do have this value populated in the table for each of the clients, so that doesn't seem to be why it's not showing up.

2. There is another value that I want to populate based on both the Client ID and the active plan ID... but if I get #1 working, maybe I can figure this part out.

[comment]

If the Services table is linked to the Clients table (based on the selected ClientID), and there is an ActiveServicePlanID field in Clients, then you can simply place it on a layout of Services.

To see actual details of the active service plan, you will need to define a relationship that matches the ActiveServicePlanID field in Clients to the ServicePlanID field in ServicePlans.