One join file or separate join files

[bluearrow]

Hello,

From a design point of view, in a set of related tables within a database: is it recommended or optymal to have multiple join files or a single one.

For instance, given:

teachers

students

classes

teachers_exams

courses

We could have:

teachers JOIN1 classes

students JOIN1 classes

teachers JOIN1 teachers_exams

students JOIN1 courses

teachers JOIN1 courses

or

teachers JOIN1 classes

students JOIN1 classes

teachers JOIN2 teachers_exams

students JOIN2 courses

teachers JOIN2 courses

Thanks

[comment]

Your question is not clear, esp. the examples. In general, a record in a join table describes a specific instance of a join between two records in the "parent" tables, e.g. Student 123 enrolled into Class 45 in Year 2010. Attaching a teacher to the same record would make no sense - unless each student in that class can have a different teacher.

Attaching a teacher to a class in another record of the same join table would make sense only if teachers and students are kept together in a single table. Even then, I am not sure such "saving" would be worthwhile.

[bluearrow]

Hello Comment,

...Attaching a teacher to the same record would make no sense.

You are right. In the first example, I was thinking in having different records, some records with teachers and classes ID (for instance) and other records with students and courses IDs (but without teachers ID). I could then have a JOIN file with different records joining different tables. The alternative would be to have different JOIN tables for each case. Does this make sense? Having only one JOIN table seems simpler, but I am not sure it is good practice or if could have negative implications of which I am not aware at the moment.

Sorry for not being clear in the examples.

Regards,

Robert

[Vaughan]

Back in FMP 6 days, when files only held one table and small furry creatures from Alpha Centauri were REAL small furry creatures from Alpha Centauri, some developers would re-use joins to keep the file count down a bit. It usually meant that the join table had many redundant fields and it made the data model more complex and confusing.

These days it's completely unnecessary to economise on join tables.

[comment]

a JOIN file with different records joining different tables

It's not good practice - it forces the join table to a denormalized state, which does have negative implications. For example, it will be difficult to enforce the uniqueness of a join record (e.g. same student taking the same class in the same year). It has no practical advantages either, since Filemaker will force you to use different occurrences of the table anyway.

[bluearrow]

Clear enough. Thank you both.

Regards