Indexing many to many relationships

[MikeLeBen]

Dear all,

it's been a while, but once you become acquainted with Access and databases, it stays with you and you begin to imagine applying all sorts of solutions to everyday problems thorugh its use.

Therefore, I am a bit a rusty and could use some advice before diving into form design.

I am currently designing an Access db application to import study plans and allow users to view/edit data and print reports.

My table structure is as follows:
Link to image

Proposte (Proposals) has a pkID which is stored as fk in CorsiEstero (CoursesAbroad) and CorsiLUISS (CoursesLUISS).

A student can submit several Proposals, some of which are approved after review by the academic committee.

Each proposal can consist of 1 to 3 CoursesAbroad, and 0 to 2 CoursesLUISS.

My goal is to be able to build a report at the end of the day where for each proposal I can see grouped CoursesAbroad and CoursesLUISS - ideally displaying none when none are present in the latter table.

Have I approached this the right way?

 

[MarkK]

I might not put CoursesAbroad and CoursesLUISS in different tables. They are all courses, correct? So if they differ by some dimension, then include that dimension as a field in the courses table, rather than start a new table, so the courses table might have a boolean field called IsAbroad, and a field called IsLUISS.

The table is for the type of thing. The fields are for the dimensions of that type. A course is a type of thing. Where it occurs is a dimension of the type.

hth