Creating two relations to the same datatable field

Are you perhaps trying to create the relations in the wrong direction?
Your CourseRelation Table has the foreign keys (N side of a 1:N relation), the Course.ID is the primary key (1 side of a 1:N relation) of the two relations.
I always forget what field needs to be dragged to which, but after doing one relation simply inspect its properties to see what is 1 and N side and if that is really correct.
If you mean the right thing but unintendedly do the wrong thing, you'll make a primary key a foreign key into two different tables, which would be weird to say the least.

Bye, Olaf.

I attached a screen shot of the designer. It looks like it recognizes which is the foreign key and which is the primary key.

I see, but that's wrong. You're tying to do one relation named Courses_CourseRelation1 to CourseID and RelatedCourseID.
You need two relations, one from Course.ID to CourseRelation.CourseID and one from Course.ID to CourseRelation.RelatedCourseID

You can't crunsh that into one relation. That's all there is to it.



Bye, Olaf.

Oh, I see.

You're right. I was able to create two relations by changing the name of the first one and then linking the other FK to Course.ID.

Thanks for the help.

Just FYI: The dialog allows adding more than one field, as you are not forced to make a single primary key field and therefore that and the foreign key might be composed from several fields. But the dialog is about one relation, it doesn't list all relations of the two tables.

Bye, Olaf.

One more thought.

I don't know if this will apply here, but if the relation should be symmetric, eg two courses relate to each other, then you need two records for the two ID combinations.
The way you have it it's asymetric as in Facebook friendships not confirmed by the one or other. Eg Course 2 may be related to Course 1, but if the mirrored record doesn't exist Course 1 isn't related to Course 2.

You may only store one relation with CourseID<RelatedCourseID and use it for both relation directions, but in queries that'll mean two joins looking up a Course.ID as CourseID or RelatedCourseID.

If it should be like family relations, where there is no one way relationship (no matter if you like it or not) I'd design a RelatedCourses table with a family or group number and CourseID. All related courses get the same group number here. If three courses relate to each other then you add their IDs in RelatedCourses with the same group number. Groups may overlap.

For example

[pre]GroupNo CourseID
----------------
1 1
1 2
2 1
2 3
2 4[/pre]

Will mean Course 1 is related to course 2 via GroupNo 1 and to Course 3 and 4 via GroupNo 2.

In your table you'd need
[pre]
CourseID RelatedCourseID
------------------------
1 2
2 1
1 3
3 1
1 4
4 1
3 4
4 3[/pre]

Bye, Olaf.