many-to-many-to-many makes for many problems

[Belmond]

Dear friends,

I usually cope well with my relational structures but I have come to a complete halt over this one. I have A,B and C tables, each with a many-to-many relationship to the other 2. The report output requires reporting from a combination of all 3 sets of many-to-many tables ie AB, AC and BC. To make matters worse, A is itself a many-to-many result of files X and Y.

However, I look at this, it looks clunky. Data normalisation seems to require that I set up AB,AC and BC tables in order to maintain data integrity but to get my reports, I first need data from all 3 tables to be consolidated in one table, say table Z. Am I on the right track?

Do I create a many-to-many of XY called A. Then create B and C then create AB, AC and BC then finally create Z and bring in relevant data from AB, AC and BC for reporting purposes?

Help!!!

[Ender]

Generally, a direct many-to-many is not a good idea. There is usually a join table in between.

Talking in ABCs and XYZs is hard to visualize. Can you give more information about the data that your tables contain? What makes a record unique in each table?

[Belmond]

OK here is an example,

A College has a number of campuses distributed around town. Each campus has many students and many courses. Each student uses many campuses and many courses. Each course has many students and believe it or not occurs on many campuses. Hence many to many to many.

Ideally, I want to enter all data on one screen which can be done using portals of course. However, I want to report data from all three tables on one report.

Here is my problem. Do I set up a single join table - campus/student/course or do I follow data normalisation theory and set up 3 join tables - campus/student, campus/course and student/course. The problem with this approach is how to consolidate the data from the 3 tables into one report.

Thank you for your interest in my problem.

[Ender]

Definitely use join tables. Below is my thought for an ER Diagram showing your structure (it's not quite the same as the Table Occurrence graphs that FileMaker 7 uses, but the structure is easier to read).

Remember, in FM7 you can access data many table away, so you could do most or your reports from the Registration table. The Resident table is if you need to keep track of which campus a Student is assigned (it wasn't clear from your example.)

Data entry is best done on separate layouts. Enter campus records in the campus table, enter course and section data through the course table. Student data entry should probably be entered separate from registrations.

Let me know if this makes sense.

[Ender]

Addendum: The Resident table is only necessary if a student could reside in multiple campuses. I don't think it's really necessary. Instead you might have a relation between Student and Campus to allow a Student to reside at one campus (use an Resident Campus ID field in Student).

[Belmond]

I get it,

Your information has been very helpful to me. I had thought that I could make 1 join file between the 3 different tables. I see from your example that this is wrong. I need to make a Registration join file and a seperate Residence join file.

Thank you very much for your help. I know what to do now.