Hi all
Congratulations for this great group, and first of all, sorry for my
bad english...

I'm trying to do a database for a music school.
The problem is that I'm really not understanding how the table
relationships work.
I read the "Filemaker 10 bible" book and the Filemaker help, but that
really didn't help much, so I must be really stupid (hehehe).

The problem is this:
I have several tables (students, teachers, employees, etc) and I want
the following feature:
when I write a class name ("piano", "flute", etc.) in a student record
(table "students" and I choose a teacher name for that class, the
student's name, number and grade (in that class) must show up in a
list (portal?) at the corresponding teacher's record.
Each student may be enrolled in several different classes ("piano",
"choir", "music theory", etc) with different teachers (or not) and
grades.
This would be really perfect if the information at the teacher's
record was grouped by class, because each teacher can teach different
classes (for example "piano" and "music theory"). Like: "Piano":
Student No.1, Student No.2, Student No.3, etc.
Music Theory: Student No.1, Student No.4, Student No.5, etc

Many thanks in advance
Please explain it easily, because I'm soooo confused with this
subject...

Pianoman
(Portugal)

On 2010-02-25 18:03:51 -0800, Pianoman <pianoman.pt (AT) gmail (DOT) com> said:

Your English is perfectly fine, and certainly better than my
non-existant Portugese.
When you read about relationships in FM and the relationship graph, you
will often come across the word "Context." When I first started
learning this in FM7, I had to simplify it for myself because I too was
terribly confused. I used a visual metaphor for myself since this is
the way I think.

I began to think of context as "where I am standing to see the data I
want to see." Then I would list the data I wanted to see, and HOW I
wanted to see it. That method, that HOW was the relationship. And a
relationship can only function through a key field or multiple key
fields. Those key fields are the window from one table to another.

So if I were in a student record, and I wanted to see the classes a
student was taking, I would know that somewhere out there was a table
of classes. Each student record has a StudentID key field. Each class
record has a ClassID key field. Looking at that, I realize that many
students can be in each class, and there is no room in the basic Class
record for many students. So a direct link between these two tables
won't work. I can't see directly from Students to Classes.

This can only mean one thing. There is what is called a Join Table
between them. This is a table, perhaps called "Enrollment" where each
record contains a StudentID field, and a ClassID field. So if a student
is enrolled in 5 classes, they will have 5 enrollment records. Each
will have the same StudentID value, but they will each have a different
ClassID.

When I make the relationship from Student to Enrollment to Classes, I
can see the data from both Enrollment AND Classes from where I am
"standing" in the Student record. The Enrollment join table feeds the
data from Classes to Students, through this two-jump relationship. So
I can put a portal using this relationship on the Student layout, and
see the 5 join table records, each with the name of class that is kept
in the Class table.

Similarly, I know there must be a table for Teachers. Teachers can
teach more than one class (unless they can't in your system) so there
is probably a join table between Teachers and Classes, perhaps called
Sections, where each record has a TeacherID, and a ClassID. Or you
could have multiple teachers teaching the same class at different
times, so that also requires a join table to exist. From a Teacher
record you will be able to show the classes they teach. From a Class
record you will be able to show the teachers who teach it, and the
students enrolled, all in portals.

When I select a class for a student, I will want to bring across the
correct TeacherID as well, so I know *which* class they're enrolling
for. Perhaps there is a section number you can include in the value
list to help you choose. The TeacherID would have to live in the
Enrollments join table, and from that same record, you can link
Students to Enrollment to Sections to Teachers, and pull that Teacher
name from that 3-jump relationship. Remember that the Section table
will also have a SectionID.

In this case, the key fields used on each side would be:

Student::Enrollment using StudentID
Enrollment::Sections using SectionID
Sections::Teachers using TeacherID

Get this arrangement of tables and relationships set up on your graph,
as described. Make up some student, teacher, and class records and make
some manual join records for Enrollment and Sections by copying and
pasting your IDs into new records. Don't worry for now as to HOW you
will select the classes or teachers for a student. Then show the data
in portals on your Student, Teacher, and Class layouts.

Once you get this working and start to understand it, come back to us
and we'll work on how to make selections for your system.

--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

On 26 Fev, 02:33, Lynn Allen <l... (AT) NOT-semiotics (DOT) com> wrote:
Thank you SO much for your reply.
I really can't believe that there is someone so nice who spends time
replying to a newbie like me.
Many thanks again. I'm going to read your answer carefully and do some
testing in the next few days.
I will report back with some (good) news soon (hopefully).

Pianoman
(Portugal)

you want to look into many-to-many relationships.
each student might follow many classes
each class might have many students
to accomplish this you need a many-to-many relationship (mtm)
to manage a mtm you need an extra table, called the join-table
in this case you could call it the student_class table
It would hold 2 fields
you already have pk_studentID and pk_classID which are auto-generated
primary keys (pk) for each table
the join table would have the fiels sk_studentID and sk_classID (sk =
secondary key)
these need to be the same type as the pk fields
inside the relationgraph join student:k_studentID to
student_class::sk_studentID & class:k_ck_classID to
student_class::sk_classID
When you want to assign a student to a class you create a new record in the
join table (student_class)
then assign pk_studentID to sk_studenID and pk_classID to sk_classID
All this is best scripted to counter any errors
with the record created you now will be able to create a layout for the
Student table
create a portal for student_class and put inside this a field from class
(like name or something similar)
the relationship starting at student will look 'through' student_class and
find all connected classes (also true the other way around)

Hope this will get you going

--
Keep well / Hou je goed

Ursus

"Pianoman" <pianoman.pt (AT) gmail (DOT) com> schreef in bericht
news:267f8f43-fa70-446d-8560-1fb37cba19be (AT) t23g2000yqt (DOT) googlegroups.com...

On 26 Fev, 22:35, "Ursus" <ursus.k... (AT) ziggo (DOT) nl> wrote:
Thanks Ursus. I've been doing some tests after reading both answers.
I think I'm going somewhere with classes and students.

Now I'm off to teachers. The process is similar, right?

Just one question: why do I need to have auto-generated pks and sks
for the ClassID? couldn't they be text (the class name like "piano",
"flute", etc.)

Thanks again
Pianoman
(Portugal)

In article
<849fa01e-4d41-4770-b586-a5a19b717dde (AT) v13g2000yqv (DOT) googlegroups.com>,
Pianoman <pianoman.pt (AT) gmail (DOT) com> wrote:

You can certainly have a field for the name of the Class, just as you
have fields for the name of the student. However, relationships should
be based on auto-generated numbers that cannot be edited by the user. If
you base a relationship on a field that can be edited, then any change
in the content of the field will break the relationship.

Also, I would add to the previous suggestions that the logical place to
record a student's grade for the course would be in the Join table. That
is also the place to record the registration date, and any other
information pertinent to the enrollment itself. So the Join table
("Enrollment" or "Registration" or whatever you call it) would need
fields to record those kinds of information.

I think I have the Student part working OK already. Thanks again for
all your help.

Now I need to do the Teacher part, but I'm not still able to figure
out which relations I need.


This is the situation I want:
Each Teacher might teach several classes (Piano, Music Theory, Choir,
etc)
Each Student might (and probably) have many different teachers (piano
teacher, Choir practice teacher, etc)
Each teacher might (surely) have many students

Some Classes are individual (any of the instruments)
Some Classes are collective (Music Theory, Choir Practice)

I want to list the students that each teacher has, grouped by class
subject and sorted by weekday and time with some extra informations
like grade, number, classroom, etc.
for example:

Teacher: Pianoman

Class: Piano
"Student no.1" grade 2, classroom 3, Sunday, 10:00
"Student no.2" grade 4, classroom 3, Sunday, 11:00
"Student no.3" grade 5, classroom 3, Sunday, 12:00

Class: Choir practice
"Student no.4" grade 1, classroom 1, Monday, 15.00
"Student no.2" grade 1, classroom 1, Monday, 15.00
"Student no.5" grade 1, classroom 1, Monday, 15.00
"Student no.3" grade 1, classroom 1, Monday, 15.00

Any help?


Many thanks in advance
Pianoman
(Portugal)

you work your way down from left to right. Create a layout based on the most
left item, being the teacher. Then create joine-tables or relatations as
needed.

Teacher::teacher_class::class::class_student::stud ent::student_classroom::classroom::classroom_day:: day

But before you go and create massive amounts of relationships a bit of
warning is in place. If you just go ahead you will end up with a
relationship graph looking much like a spider (therefore called a spider
graph, sometimes also chaotic-graph). These a very difficult to work with
as they get larger and larger. But there a a couple of other ways to go
about that you need to learn first. And I would like to urge you to take the
trouble before your product is finished. Once you have made a choice it will
be very difficult to change your ways. So please read the approach on graph
modelling. found at:
http://developer.filemaker.com/conte...odeling_en.pdf
Try them out and make a better choice (probably a mix between squid and
chaotic in your case)

It will take some time and trouble but imho it is really worth the trouble.

--
Keep well / Hou je goed

Ursus

P.S. also I could recomend the following

http://www.filemaker.com/downloads/p..._ConvNov05.pdf

--
Keep well / Hou je goed

Ursus

"Pianoman" <pianoman.pt (AT) gmail (DOT) com> schreef in bericht
news:0abde89b-650b-469a-beb4-658716ffc2d3 (AT) g28g2000yqh (DOT) googlegroups.com...

In article <76f01$4b8a4e92$535399fa$17600 (AT) cache3 (DOT) tilbu1.nb.home.nl>,
"Ursus" <ursus.kirk (AT) ziggo (DOT) nl> wrote:

I agree very strongly with Ursus.

I started out doing relationships in FIlemaker 7 and later, just adding
on tables and table occurrences as needed to get the results I needed,
without regard to the orderliness of the relationship graph. Filemaker
allows you to do that, and the solution works. However, as my solutions
got more complex, the relationship graph got more confusing, and it got
more difficult to know what I was doing when creating portals, value
lists, calculations and scripts.

I finally understood the value of two basic things, that seem mundane,
but are very important for a solution of any complexity:
-Use of the "anchor-buoy" or "squid" model for relationship diagramming;
-Use of a standardized naming convention for Table Occurrences.

I now build all new database solutions using these methods, and I have
even gone through the very laborious process of converting some of my
earlier solutions to follow these conventions.

These methods are described in some papers on the FileMaker web site,
that Ursus already referred to. There are other useful papers as well.

To summarize the basic concepts:

Each table of the database should have a base Table Occurrence (TO),
named the same as the table. These base table occurrences should be
lined up one below the other on the left-hand side of the relationship
diagram. I usually put them in alphabetical order by name. These base
TOs should NOT be connected to each other by relationships.

Each of these base TOs then becomes the "anchor" for an "anchor-buoy"
array of TOs, arranged to the right in a fanning-out pattern. This kind
of array is also called a "squid." The arrangement and content of each
array is based on the information needed to support layouts and
operations connected to the "anchor" TO.

All the layouts in your solution should be based on one of these base or
anchor TOs. No layouts should be based on any of the related "buoy" or
"tentacle" TOs.

For each need you have in the anchor TO for related data, you create a
new TO of another table, and connect it to the anchor TO in the
appropriate way to get the related info you need. The connection can be
through an intermediate TO, depending on the logic of your solution.

All calculations and script steps, which are all tied to layouts, will
then be based on one of these anchor TOs, taking related information as
needed from one of the other TOs in the array of TOs that are related to
the anchor TO.

The TOs in each array are generally related to the Anchor TO in a simple
way, as their sole purpose is to feed information to portals,
calculations, value lists and script steps that are based on one or
another layout of the anchor TO.

Within each anchor-buoy or squid array, the TOs should be named by a
method that identifies the anchor TO and the TOs in the chain between
the anchor and the TO you are naming.

For example, in you solution for Teacher, Student, Class:

You need 5 tables:

Teacher
Student
Class
TeacherClass
StudentClass

TeacherClass and StudentClass are Join tables that assign teachers to
classes in a many-to-many relationship, and students to classes in a
many-to-many relationship.

Each of these 5 tables would have an anchor TO in the relationship
diagram, and each would have at least one layout. These 5 anchor TOs
should be arranged down the left-hand side of the relationship diagram.

Then create other TOs and connect them to the anchor TO as needed to
produce the information you want for portals, calculation, value lists
and script steps that are based on the anchor TO.

Each TO in the array except for the anchor TO is a new, uniquely-named
TO of one of the five tables. It must NOT simply be the anchor TO for
that table moved to the array, but a new TO; the anchor TOs must be left
alone on the left side of the diagram, to form the basis for their own
arrays or relationship groups, NOT connected to any to the other anchor
TOs.

The papers on the FileMaker web site suggest some naming conventions for
the various TOs in an anchor-buoy array. I use one of the suggested
naming conventions. Whatever naming convention you use should indicate
the anchor TO of the array, and the TOs between the anchor TO and the TO
you are naming, and of course should indicate the name of the Table on
which the TO is based. That way you have an easy time of figuring out
what you are doing with information from related tables when you create
a portal, calculation, script step or value list. Whatever naming
convention you use, stick with it, so you don't have to figure out what
you did when you look back at one of your solutions.