Newbie Ultra Confused with Relationships

[dadudette]

[color:purple]Hi Everyone,

I've tried to hunt down a very basic instruction for creating a relationship or more and though there's stuff out there, it's not basic enough to get me started. I'm so confused!

I'm the sole administrator for a small school and I want to create a database to make life easier for myself (it's not working...yet!). I have students, I have parents, I have staff, I have classes, and all can intermingle. One parent can have several students and one student can have several (up to four) different parents and would always have at least two. One class can have two teachers and loads of students, of course, and a parent and teacher can even be one and the same. I thought that a students (or children) table and a parents (or adults) table would be good, so that I could enter the individuals and then use a drop down or something to choose the parents to attach to a student and vice versa, and have all the parents's data come in if desired also - or maybe one table for people and another for classes or something...but I don't know. I don't want to have to enter a man's address once for being a person and another time for being a student's parent.

For example, in a student's file I'd like to access the list of parents and choose one for the mom, one for the dad, etc, and have each parent's address come into the student's file also, so I don't have to retype it.

I just can't understand how to do this.

I hope that makes sense and that someone out there can get me going. If I don't make sense, please ask questions if you're willing to help me break into the world of relationships. It's so confusing - and I'm not even very dumb.

Thanks ever so much, folks...

[dadudette]

Oh, and I also need to be able to attach students to other students (siblings) and parents to other parents (couples). I don't know if that's important in answering my :, but it certainly complicates matters! Any geniuses out there looking for a challenge will love me today.

[Fenton]

These are all classic problems. The school/parent situation is one of those where the data is fairly simple, but you really have to understand relationships to build a structure that can handle all the connections. You are asking the right questions; that is a good first step.

Each "entity" needs its own table. I think you're right that you really only need a table for Students and one for Adults (dumb name, but what else :-?). Optionally you could have a separate one for Teachers. But it is probably easier to separate parents from teachers when needed within the same table than it is to update a changed phone# in 2 tables. It depends.

Within these tables there will be an auto-entered serial ID field, which FileMaker will increment. This is important. It is the backbone of the relationships.

You will also need tables to use as "join" tables. This will allow the "many of this to many of that". These tables may have only 2 fields. Example: StudentID, AdultID. This will allow a student to have multiple parents (common :-), and a parent to have multiple children. It doesn't really matter how many.

When you add a parent to a student you do it by adding a record in the join table, with both IDs. It would know one of them, if you created the link in a "portal", with "[x] Allow creation of related records" in its relationship (on the join table's side).

But you'd have to pick the other ID, via a drop-down list probably. Fortunately you said it was a small school. Also, in FileMaker 8 you can "Show only the 2nd field" in a value list, so you can show only the name, hiding the ID. It still PUTS the ID in the FIELD, not the name :-!

You DO NOT use names for relational links, unless there is no possibility to use an ID.

You can look at the join table from either side, from Students (add a parent) or Adults (add a child). The only real difference is which ID has to get "picked."

I would use a separate table for Addresses. This allows you to use one address for multiple people, ie., a "household". Same for Phone numbers, a separate table. Perhaps even a join table also. That way multiple people could share the same phone#.

[dadudette]

Thank you SO much! When I first read your response it all flew over my head like a leer jet, but as I picked it apart and used some trial and error I think I got it mostly figured out! My problem's not solved, but I'm a LOT closer than I was, thanks to your kind help! I appreciate it a lot.

[Fenton]

Yes, I did lay it out pretty hard and fast. But it looked like: 1. You have a fairly ambitious project for a beginner, and 2. You were asking the right questions.

School databases are great solutions for relationships. Because the data itself is not that complex, but almost everything is inter-connected. So, if you get the relationships set up correctly, everything else will go fairly smoothly.

You've also foreseen the problems that are caused by people in families, who will tend to live in unpredictable groupings, move, change their names, etc.. You need to be able to view them as individuals sometimes, and as a group at an address other times. It's best to set up your database right from the start to handle these changes.

If you can put together an example file of what you're doing, along with description of problems you're having, we'd be able to offer more specific advice and illustrations.

[dadudette]

Thank you! I'll work on putting together something that can express what I've got and what I want to do with it. I'm learning at lightning speed here, but boy oh boy, it's so complex!!

I'll be back...

[T-Square]

Dadudette--

Welcome to the forums! Fenton is right--you're asking the right kinds of questions.

Just to show you that there are many ways to answer the problem you're describing, I'll note the discussion at:

http://fmforums.com/forum/showtopic.php?tid/174925

I bring this up because in this example, you certainly can separate children, parents and teachers (as Fenton has suggested), but you could also decide that a person is a person (in database-speak, you have a single "person" entity), and define a person to have various relationships to other people.

For example, one person might have several children and simultaneously be a teacher. If you have separate tables for Parents and Teachers, you'd (most likely) end up with the same person entered in each table. You could avoid that duplication by using a relational approach.

Putting everyone into one Persons table adds complexity in the relationship graph, however, and also requires different approaches for relating one person to another. Consequently, it may not be worth your time to work out all the steps involved.

With regard to the not re-typing addresses issue: read in the help about auto-enter and lookups; you can have FileMaker "transfer" information from one record to another on a field by field basis.

However you go about it, best of luck, and I hope we can be of help!

David

[mkruter]

Wow! This is exactly the same problem that i am trying to solve!

I purchased Filemaker about 6 months ago and couldn't get past the basics of relating tables. I watched all of JMO's tutorials but it isn't the technical stuff i struggle with, it's the basic concept. i just know once i can comprehend the overall idea i will make some beautiful solutions. I emailed JMO on several occasions but have no idea what he is talking about. He recommended taking a class but i cant afford that! i couldnt even afford the VTC membership (cancelled a couple of months back).

So now I have decided to try and learn again. i came to the forums and was very happy to stumble upon this thread. However, unlike Dude, i have not been able to figure out what to do from the responses given.

Is it possible someone would be willing to take my hand and walk me through the process? A personal phone call would be great. i really think i only need a half hour to "get it". Currently, i understand that relationships are based on the serial number generated by filemaker but i dont understand why or how.

Thank you so much,

Marc