Creating two relations to the same datatable field

[gib99]

Hello,

I'm working in the dataset designer in Visual Studios 2012 and I would like to create two relations between two tables. The one table is the "Courses" table, and the other is the "CourseRelation" table. Basically, the CourseRelation table relates one course to another. So I want to create two relations between the two tables. For the first relation, I want to relate CourseRelation.CourseID to Courses.ID, and in the second relation, I want to relate CourseRelation.RelatedCourseID to Course.ID.

But when I do this in the Relation dialog, it says:

"Cannot create a Key when the same column is listed more than once: 'ID'

These kinds of relation aren't impossible and I can't be the first one to try it. How can I accomplish what I want to accomplish?

 

[Olaf Doschke]

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.

 

[gib99]

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

 

[Olaf Doschke]

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.

 

[gib99]

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.

 

[Olaf Doschke]

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.

 

[Olaf Doschke]

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.