multiple primary keys
I am working on a database to manage courses (at a university) which are mapped to templates, which are mapped to modules (groupings of courses), which are again mapped to study programmes. An example:
teacher A wants to offer the course "introduction to simulation" as an "introductory course" (template 1) in a module called "modelling and simulation" and at the same time as "special lecture" (template 2) in another module called "introductory courses for foreign students". The module "modelling and simulation" is part of the study programme in "Geoinformatics", the other one part of the study programme "Erasmus students".
Modules may have several mandatory and/or optional templates and in order to identify the different courses as pertaining to a specific templates, the templates within a module are numbered. It may also be that a specific module can be used in more than one study programme.
What I have so far works fine in terms of assigning the different courses via a long match list to a specific study programme. However, now I need a way to produce a pdf to show the mappings in a specific way. And what works fine in one direction suddenly does not work in the reverse direction, i.e. from study programme to courses. The bottleneck seems to be the match table from templates to modules (red in the picture), where in addition to the two foreign keys kf_modul_ID and kf_template_ID, I also need the template ordering number called Z_Template_OrdNr in the appropriate module.
I cannot figure out how to include the three identifying fields into one primary key (I do have an auto-numbered kp for identification). Matching the tables works fine with the two foreign keys, but the identifying piece in that match is the ordering number which is at the moment an attribute of that match - this is certainly not how it should work. Without the ordering number, the path from module to template has several solutions and (as is proper) Filemaker uses the first matching one.
Below is how my database looks at the moment. I apologise for the German / English mixture of terms. As you can see I have 5 "data tables" (Personen, Lehrveranstaltungen (courses), Templates, Module and Studiengaenge (study programmes) and 4 match tables (Z_Dozierende_LVA, Z_LVA_Template, Z_Template_Modul, and Z_Modul_Stg) because all of the relationships are many-to-many.
I'm working with Filemaker Pro 11.0v4 and the database is uploaded to a server with Filemaker Server 11.
Can you help me?