Understanding relations in tables Dynamics AX 2012

Today I am going to explain the relations between tables by implementing a real time simple scenario.  Assume we have a student who is enrolled in a course. We want to keep the track record of the students who are enrolled in one or many courses. We would require two tables one for Student and other one for Course. Multiple students can be enrolled for the one course and one course can be taught to multiple student thus relationship exists between Student and Course is "many-to-many".  Therefore, we are going to add one more table naming it "StudentCourseEnrollment". Given bellow would be the ERD.

In order to implement this scenario in Dynamics AX 2012. We would first need to create three tables Student, StudentCourse and StudentCourseEnrollment.

While creating StudentCourseEnrollment, we have to add the relations of both Student and Course table,

• Now while referring to MSDN post where there is detail description of properties of table relations. Here we would require to mark "Validate = Yes" because we would never like any body to play with data ignoring the relationship of the records. For example, we would not like some one to delete the data in Student (parent) table whose reference is in the StudentCourseEnrollment (child) Table. However, It also depends on the delete action we are going to define on the parent table. In other words, marking Validate yes will always take care of relationships and perform the validations accordingly.
• I choose the option "ExactlyOne" for RelatedTableCardinality property because we cannot allow a null value in any of the records for the Student field of StudentCourseEnrollment table.
• It is not necessary that every Student or Course record must exist in the StudentCourseEnrollment table, there can be multiple records related to single record of the student or course or zero, keeping this thing in mind, I am going to set the Cardinality property of the relation to "ZeroMore".
• Now there is property named relationship type. We have six options which one would be best suited for our scenario.  Should I choose "Composition" here? since if we are going to delete a student record, all his course enrollment should be erased from Course Enrollment and same goes for Course. but wait since we can add delete action to enforce the relationship between Student and StudentCourseEnrollment table so why not choose  "Aggregation" here. I would also need your input on this as well.

After we set all important properties for relation we defined, click new and see there are four options (Normal, Field Fixed, Related field fixed and Foreign Key).

To gain better understanding for all of the four options, again check the MSDN. In my case I have selected Foreign key => Primary Key as we have normally been doing in Sql Server.

I have tried a lot to make this post brief and useful, I would explain the delete actions related to this particular scenario in my next post. Have good day!