Many-to-many relationships (XP, 2000)

Topic

New Reply

I am having some trouble understanding how to implement many-to-many relationships. I know conceptually that this calls for an intermediary table with a one-to-many relationship with each of the other two tables. However, I can

Reply | Quote

Replies

Trying to do this is complicated. Let’s take a simpler example first: two tables Table A and Table B with a many-to-many relationship. This is implemented by creating an intermediary table Table D with a composite primary key consisting of the combination of two number (long integer) fields that are linked to the primary keys in Table A and Table B.

Table A has a one-to-many relationship with Table D, and Table B also has a one-to-many relationship with Table D. Together, these constitute a many-to-many relationship between Table A and Table B.

We don’t create a form based on Table A with a subform based on Table B.

Depending on the perspective from which you want to look at the data, you would create either a main form based on Table A with a subform based on Table D (or on a query that includes Table D and Table or a main form based on Table B with a subform based on Table D (or on a query that includes Table D and Table A.)

An example may make things clearer:
Table A = students, with primary key StudentID
Table B = courses, with primary key CourseID
Table D = participation, with a primary key consisting of the combination of StudentID and CourseID.

Each record in Table D represents a student participating in a course.

There can be many records with the same StudentID (but all different CourseID’s) – they tell us which courses a particular student attends.

There can be many records with the same CourseID (but all different StudentID’s) – they tell us which students attend a particular course.

You could have two forms:
1. A form based on the students table, with a subform based on the participation table, displaying the courses attended by a student. Main form and subform are linked by StudentID.
2. A form based on the courses table, with a subform based on the participation table, displaying the students attending a course. Main form and subform are linked by CourseID.

Reply | Quote

Hans

You suggest that the intermediate table have a composite primary key, consisiting of the two foreign keys.

I often want to allow for the possibility of multiple entries in the intemediate table for the same pair of values in the the Main tables. For example, a patron may borrow a book more than once, or a student undertake a course many times (perhaps until they pass!). Your design does not allow this, so instead I just create another autonumber field in the intermediate table and set this as the key.

Is there any downside to doing this?

Reply | Quote

John,

I would decide that on a case-to-case basis. I might create yet another table with a one-to-many relationship to the intermediary table, or I might use the keys from the main tables plus a sequential number, or an autonumber (your approach.) Sometimes a complete normalization is just too cumbersome.

Reply | Quote

John,

I would decide that on a case-to-case basis. I might create yet another table with a one-to-many relationship to the intermediary table, or I might use the keys from the main tables plus a sequential number, or an autonumber (your approach.) Sometimes a complete normalization is just too cumbersome.

Reply | Quote

Hans

You suggest that the intermediate table have a composite primary key, consisiting of the two foreign keys.

I often want to allow for the possibility of multiple entries in the intemediate table for the same pair of values in the the Main tables. For example, a patron may borrow a book more than once, or a student undertake a course many times (perhaps until they pass!). Your design does not allow this, so instead I just create another autonumber field in the intermediate table and set this as the key.

Is there any downside to doing this?

Reply | Quote

Okay Hans, sorry but I still don’t get it. I created the DB you used as an example but can’t figure out how to actually use it. I have attached it for your comment. I guess the part I am confused about is updating multiple tables at the same time. Please see attached.

Reply | Quote

General recommendations:
– Turn off “Track Name AutoCorrect Info” in the General tab of Tools | Options… in each database you create.
– Set the Subdatasheet Name property of all tables to [None].
– Avoid creating duplicate indexes; Courses, for example, had two indexes on CourseID.

I have created the subforms for you and placed them on the main forms. They are very simple continuous subforms, with a combo box to let the user select a course or student. See attached.

Reply | Quote

I very much appreciate the sample DB you attached. I have studied it and it will launch me on a total redesign of my DB. However, as I look at the forms & subforms, I don’t see where the information in the subforms come from. For example, in looking the Courses form and the subform (sbfStudents), I cannot find how the students’ names appears in the subform. The subform has but one field and that is the StudentID field. I see that it uses the query (qryStudents) as the row source but how is the student name retrieved? The query has two columns but the subform field is bound to column 1. I changed it to ‘0’ to see what effect, if any, and it had no effect.

Thanks you.

General recommendations:
– Turn off “Track Name AutoCorrect Info” in the General tab of Tools | Options… in each database you create.
– Set the Subdatasheet Name property of all tables to [None].
– Avoid creating duplicate indexes; Courses, for example, had two indexes on CourseID.

I have created the subforms for you and placed them on the main forms. They are very simple continuous subforms, with a combo box to let the user select a course or student. See attached.

Reply | Quote

The combo box StudentID has the query qryStudents as Row Source. This query has 2 fields:
– The first field is StudentID.
– The second field is a calculated field that concatenates the last and first names of the student, separated by a comma and a space:

SName: [LName] & “, ” & [FName]

The combo box has Column Count = 2 (corresponding to the two fields/columns in the query) and Column Widths = 0″;1″.
This means that the first column has width 0″, which effectively hides it, while the second column has width 1″.
The combo box displays the first non-hidden column, i.e. the column corresponding to the student name SName.
Since the Bound Column = 1, the value stored by the combo box is not the student name displayed in the second column, but the value of the hidden first column, which corresponds to StudentID.

Reply | Quote

General recommendations:
– Turn off “Track Name AutoCorrect Info” in the General tab of Tools | Options… in each database you create.
– Set the Subdatasheet Name property of all tables to [None].
– Avoid creating duplicate indexes; Courses, for example, had two indexes on CourseID.

I have created the subforms for you and placed them on the main forms. They are very simple continuous subforms, with a combo box to let the user select a course or student. See attached.

Reply | Quote

Okay Hans, sorry but I still don’t get it. I created the DB you used as an example but can’t figure out how to actually use it. I have attached it for your comment. I guess the part I am confused about is updating multiple tables at the same time. Please see attached.

Reply | Quote

Trying to do this is complicated. Let’s take a simpler example first: two tables Table A and Table B with a many-to-many relationship. This is implemented by creating an intermediary table Table D with a composite primary key consisting of the combination of two number (long integer) fields that are linked to the primary keys in Table A and Table B.

Table A has a one-to-many relationship with Table D, and Table B also has a one-to-many relationship with Table D. Together, these constitute a many-to-many relationship between Table A and Table B.

We don’t create a form based on Table A with a subform based on Table B.

Depending on the perspective from which you want to look at the data, you would create either a main form based on Table A with a subform based on Table D (or on a query that includes Table D and Table or a main form based on Table B with a subform based on Table D (or on a query that includes Table D and Table A.)

An example may make things clearer:
Table A = students, with primary key StudentID
Table B = courses, with primary key CourseID
Table D = participation, with a primary key consisting of the combination of StudentID and CourseID.

Each record in Table D represents a student participating in a course.

There can be many records with the same StudentID (but all different CourseID’s) – they tell us which courses a particular student attends.

There can be many records with the same CourseID (but all different StudentID’s) – they tell us which students attend a particular course.

You could have two forms:
1. A form based on the students table, with a subform based on the participation table, displaying the courses attended by a student. Main form and subform are linked by StudentID.
2. A form based on the courses table, with a subform based on the participation table, displaying the students attending a course. Main form and subform are linked by CourseID.

Reply | Quote

okay, I finally got it. Love it when the light comes on. Thank you.

Reply | Quote