First time setting up relationships. Need help

[TimMcB]

I am using FMP 5.5 on a G4 Mac running 10.2.6. I am a volunteer with my local county public school system. I have offered to help them set up a database that would show all 120 schools. The main purpose of this database is to track the textbooks being used in each class for each subject, including online contact information for publishers and individual textbooks (where it exists). This information will then be provided to the webmaster volunteers for each school's web site so that they can set up a page that has all classes/subjects listed for each grade level with textbook names, publisher contact information - especially links to sites for individual books that give online versions, study guides, practice tests, etc. It will be a great resource for parents, teachers and students when completed.

I have created the basic database file and have setup the basic records for all 120 schools. (Name, address, phone, type of school, grades involved, principle name, email address, etc.) What I am looking for is the best way to go from here. School principles will be provided with a form to fill out about their school. It will be sent to me. From there I need to set up records for each school by class/subject and grade. A record for 6th grade math at one school would be different from the record for 6th grade science classes in the same school. And obviously it would be different for 6th grade classes in another school.

I could type all information into each record as it is created. But if I am typing in information for the same school - just different grades and classes/subjects - then a lot of the information is going to be the same. My question is: what is the best way to reduce what I have to type into the basic school fields for records that relate to the same school? Isn't there an easy way to set up the database so I don't have to type in school address, principle name, school type, etc., each time a record for the same school is being created?

I thought I could accomplish this by setting up relationships but I have never done this and just can't figure it out on my own. There must be a way to reduce all the typing I would have to do but I don't know how to do it. I have attached a copy of the basic file I have created so you can see what I have done to date. I did think about using repeating fields but different schools have different numbers of classes/subjects and grades so I thought having a separate record for each grade/class would be best. But I am open to suggestions.

Would appreciate any help any of you can provide.

[CobaltSky]

Hello Tim,

Basically, each piece of information should be located in only one place in the database - and should be referenced there from wherever it is needed elsewhere in the database. That way, you only ever enter any piece of information once. That is the beauty of relational database systems and essentially, that is what they are *for*.

The basic principle is fairly simple and once you grasp it you will be able to re-apply it throughout your database. I'll use an example.

You will have a record for each class in each school in a file - say Classes.fp5. Elsewhere, you will have a record for each school which holds all the 'core' details about the school as a whole - let's say Schools.fp5.

Now let's suppose that you give each school an identifying number (let's call it 'SchoolID'). On each record in the Classes.fp5 file, you can now enter - in a single field, the SchoolID of the school that the class belongs to. Once you've done this, you will be able to create a relationship which matches the SchoolID field in Classes.fp5 to the SchoolID field in Schools.fp5. This is done via the 'Define Relationships' command on the File menu.

Once you have such a relationship in place, each record in Classes.fp5 will be able to 'see' all the data for the corresponding school in the related (matching) record in Schools.fp5. When you are placing fields on layouts in Classes.fp5, you will be able to select fields from the corresponding School record (selected via a drop-down menu of relationships at the top of the Specify Field list dialog). That means that the name of the School will be stored only once - in a single record in the Schools.fp5 file, but can appear simultaneously on evey class record for that school in the Classes.fp5 file. With such a relationship in place, if you edit a School name once in the Schools file, the change will instantly be seen everywhere the name is displayed - literally hundreds of places throughout the database, including all the class records that belong to that school.

I suggest that you start simple - with a single relationship such as the one I've outlined above. The relationship above is what is called a 'many to one' because many classes are 'relating' to the same school. Play with it and pretty soon you will begin to get the feel of it.

When you do, the next step will be to go into the Schools.fp5 file and create a relationship that is the reverse of the one described above. Again, it will match the SchoolID fields together, allowing each school record to 'see' all the related class records for that school. Once the relationship is in place, you will be able to place a 'portal' on a layout in the Schools.fp5 file, link it to the Classes relationship and you will then be able to display a scrolling list of all the classes for that School. A 'one to many' relationship.

Once you get the idea, you will see that you will require a separate file for Books and probably another one for Teachers. The Teachers file will also need a field in which the SchoolID can be placed. When that is done, you will be able to create a relationship from Teachers.fp5 back to Schools.fp5 - and again, one from Schools.fp5 to Teachers.fp5. Pretty soon, you will be viewing a scrolling list of teachers for each school, also displayed in a portal in the Schools.fp5 file.

From there the plot thinckens. On each record in the Classes.fp5 file, you will also want to have a field for TeacherID, and that will allow you to reference all the details about a teacher from the class record. And you will want a list of the BookIDs of books for that class - so that the relevant book details can be viewed in a portal in the class record. Again, with this structure in place, if you make a correction to the name of a book, that correction will instantly appear in every booklist for every class where that book is used in every school in the database.

Hopefully the above will give you some ideas with which to make a start. Once you do, you may well have further questions. In which case I daresay we'll hear from you again.

[Riley Waugh]

That was one of the best, direct, concise, easy to follow outlines of relationship building that I have read... well done.

BTW, does relationship building work better with sensitive types?

[CobaltSky]

Riley Waugh said:

BTW, does relationship building work better with sensitive types?

Undoubtedly...

[TimMcB]

Ray:

I must agree with Riley that your explaination took a very unknown subject (unknown to me anyway) and made it very simple to understand.

It looks like I first must make my initial database, what you called "Schools" my core database file. So I will simply change the name of the database file to "WCPSS Schools". WCPSS stands for Wake County Public School System. From there I need to set up my next database, which in your description would be called "classes". However, because textbooks are done first by grade than by subject/class I will probably call the second file "WCPSSGrades". Also, because it is my understanding that the same textbook is selected for all classes for the same subject for the same grade then it will not be necessary to track things by individual teachers. When you would go to a specific school's web site to check out the books being used you would simply select, for example, 6th grade basic math. The resulting link would be valid for any parent of any child that is taking 6th grade basic math. Someone with a child taking AG (Advanced & Gifted) Math would find a different book listed with a different link.

At least that is the plan.

Tomorrow I will start working on the changes and additions you have suggested and will report back shortly.

Thanks for the help.

[CobaltSky]

On Thursday, September 25, 2003, at 06:42 AM, TimMcB wrote:

OK, I have set up four databases: WCPSS Schools; WCPSS Grades; WCPSS Teachers & WCPSS Books. I have created relationships between the following databases:

For School ID - relationships between the school db and all of the others and then back the other way.

For Teacher ID - relationships between the teacher db and schools and books and grades and back the other way.

For ISDN Book ID - relationships between the book db and the tother three and back the other way.

So now I face what to me is the most confusing part of setting this database up: getting data on each teacher for each grade and subject into the database.

So far I have prepared a letter to go to all principals of 127 schools within the WCPSS. They are being provided with a MSWord form that will hold information about each grade,subject and teacher including the name of the book being used for each class and the publisher's name, main URL, the book ISDN number and the edition.