How to model this relationship?

Question

First of all I am making it clear that, I am not using any OR Mapper framework to solve this problem, coz I need to understand the model from the ground up.

OK. Let's see the table designs:

Course - table

------------------
 ID | CourseName
------------------
 1  |  C++
 2  |  Java
------------------

Teacher - table

-----------------------
 ID |   TeacherName
-----------------------
 1  |   Professor X
 2  |   Professor Y  
-----------------------

CourseTeacher - table

---------------------------------------------
 ID | CourseID | TeacherID | ClassTime
---------------------------------------------
 1  |   1      |     1     | Monday    10:55
 2  |   1      |     2     | Thursday  10:55
 3  |   2      |     1     | Tuesday   11:45
 4  |   2      |     2     | Wednesday 11:45
---------------------------------------------

How should I design my classes in C#?

There can be another problem like is:

User - table

------------------
 ID | UserName
------------------
 1  |  a
 2  |  b
------------------

Role - table

-----------------------
 ID |   RoleName
-----------------------
 1  |   c
 2  |   d
-----------------------

UserRole - table

---------------------------------------------
 ID | UserID | RoleID | Remarks
---------------------------------------------
 1  |   1      |     1     | xy
 2  |   1      |     2     | yz
 3  |   2      |     1     | zx
 4  |   2      |     2     | xx
---------------------------------------------

Answer 1

In general, when you have a many-to-many relationship in your domain model, the access patterns in your code will either be from one side of the M2M or from the other...

For e.g. If you have a structure containing people, each of whom has made a number of purchases at your retail outlets, you might as a rule want to access those transactions thrpugh a person, not through the product. in other words, you might more often want to know what products a specific person purchased, not what people purchased a specific product. So in this example you would simply have each person class contain a property which is a collection of products. There would be no need to access the person class through the Product class.

Another e.g. If you have a a structure containing people, each of whom subscribes to a number of magazines.

Now if you are the publisher, you might as a rule want to access those transactions through the magazine, not through the person. in other words, you would much more often want to know what people subscribe to a specific magazine, not what magazines a specific person subscribes to. So in this example you would put a Subscribers collection property in the Magazine class.

Answer 2

I thinkt that you actually have a hidden third concept there: a ClassSession, or something like that. Each ClassSession would have a Teacher, a Course and a DateTime.

public class ClassSession
{
    public Teacher Teacher { get; set; }

    public Course Course { get; set; }

    public DateTime Time { get; set; }
}

Answer 3

My sample:

interface ICourse
{
    string Name { get; set; }
}

interface ITeacher
{
    string Name { get; set; }
}

interface IClassSession
{
    ICourse Course { get; set; }
    ITeacher Teacher { get; set; }
    DateTime Scheduled { get; set; }
}

interface ITransactionalEntity
{
    int ID { get; set; }
}

//sample class implementation
class Teacher : ITeacher, ITransactionalEntity
{
    private int _ID;
    public int ID
    {
        get { return _ID; }
        set { _ID = value; }
    }
    //implement interfaces
    public void Save(SqlTransaction tran)
    {
        if (this.ID > 0)
            //insert
        else
            //update
    }
}

class ClassSession : IClassSession, ITransactionalEntity
{
    //implement interfaces
    public void Save(SqlTransaction tran)
    {
        this.Course.Save(tran);
        this.Teacher.Save(tran);
        if (this.ID > 0)
            //insert
        else
            //update
    }
}

...
List<IClassSession> list = new List<IClassSession>();
ClassSession cs = new ClassSession();
cs.Course = new Course(courseID);
cs.Teacher = new Teacher(teacherID);
classlist.Add(cs);
SqlTransaction tran;
...
foreach(IClassSession classsession in list)
    classsession.Save(tran);
...

and you can do whatever you want, Linq it, etc. Use your imagination. Not everything is written here, just as an idea.

Answer 4

If you are already comfortable with parent-child relationships then I would suggest that you try to model them as two seperate parent-child relationships. For example have one screen that edits teachers and allows them to add existing courses. On the flip side you can have a course screen that allows them to add existing treachers. Following this advice should make your work easier. From a class design, you would model it the same way. The Course class would have a method to add teachers to the internal list of teachers. The Teacher class would also have a method to add Courses to the internal list of courses. The physical structure of the classes really depends on how you plan to persist the objects. File based persistence, xml serialization and database persistence can have different internal object structures. There are also class differences if you are modeling from an entity level or from a business layer level. In any case, hopefully I have given you enough ideas to run with it.