Correct Training database

[agibsonsw]

Hello. Access 2003.

Someone is creating a training database to log attendance at training courses. If we have a tblStaff including a StaffID (pk) and tblCourses with CourseID (pk) listing the course titles we should create a join table for a Many-to-Many relationship.

The join table should include the StaffID, CourseID, and DateOfAttendance. To correctly implement the M-T-M relationship the combination of these three fields should be the primary key within this join table - a staff member cannot attend the same course twice on the same day.

In this correct? What's the difference between doing this and making the combination of these fields a unique index (without being primary)? Would this still result in a (valid) many-to-many relationship?

In addition, suppose some courses are half days. Would it be sensible to add this fourth indicator to the multi-field primary key?

Andy.

[HansV]

The important part is that there is a unique index on the three fields. It's not essential that it is the primary key - you could also use an AutoNumber field as primary key.
Using the combination of the three fields as primary key has the advantage that it's the logical unique identifier, and that Access doesn't have to maintain two unique indexes on the same table.
Using an AutoNumber field as primary key is handy if you need to create one-to-many relationships from the join table to other tables - if you use a three-field primary index you also need to create three fields for the foreign key in the other tables. This quickly becomes cumbersome.

If there are half-day courses and the user can attend a morning course and an afternoon course on the same day, you'd need an additional identifier in the composite unique index.
And if you have multi-day courses, you need extra logic (not built into the unique index) to prevent overlapping courses.

[agibsonsw]

Thank you once again for the clarity of your explanation.

If a unique index were not defined would this mean (apart from the fact that incorrect data could enter the database!):
The many-to-many relationship is not correctly constructed, or
It won't work properly? That is, queries will fail.

Andy.

[HansV]

The problem would be the ability to enter multiple records for an employee for the same day. This would cause totals queries to return technically correct but unintended counts, sums etc. But queries wouldn't fail.

[agibsonsw]

Thank you. The fact that incorrect data can enter into the database should be reason enough to 'apply the rules'.

Andy.