many_to_many relationship, middle table property problem

Yii 1.1 General Discussion
1

Hello,

I have 3 tables:

  1. teacher

  • id

  • teachername

  1. teacher_course_assignment

  • teacher_id

  • course_id

  1. course

  • id

  • coursename

  • description

So every teacher has many courses and every course can belong to many teachers. That’s clear and easy.

Problem: The teacher should be able to set the price for his course(s). So that later the students can book a course from the teacher.

My 3 ideas:

  1. add a ‘price’ property in the table ‘course’ - But then the course has always the same price. That’s wrong.

  2. add a ‘price’ property in the table ‘teacher_course_assignment’ - In another thread I read this isn’t a possible solution because then it’s 2 times a ONE_TO_MANY relationship.

  3. I read in another thread that you should define a new entity or a ‘relation entity?’. Maybe a new table 'course_property(id, course_id, price) It’s like ‘teacher has_many courses, course has_many prices’. How is this done? I think I’m on the right trace. I’m relatively new to Yii AND PHP AND databases.

Thank you for your help.

These is the database (SQL) structure and data:


CREATE DATABASE IF NOT EXISTS `databaseteachercourse` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `databaseteachercourse`;




DROP TABLE IF EXISTS `course`;

CREATE TABLE IF NOT EXISTS `course` (

`id` int(11) NOT NULL,

  `coursename` varchar(64) NOT NULL,

  `description` varchar(256) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;




INSERT INTO `course` (`id`, `coursename`, `description`) VALUES

(1, 'course1', 'this is course1'),

(2, 'course2', 'this is course2'),

(3, 'course3', 'this is course3'),

(4, 'course4', 'this is course4');


DROP TABLE IF EXISTS `teacher`;

CREATE TABLE IF NOT EXISTS `teacher` (

`id` int(11) NOT NULL,

  `teachername` varchar(64) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;




INSERT INTO `teacher` (`id`, `teachername`) VALUES

(1, 'teacher1'),

(2, 'teacher2'),

(3, 'teacher3'),

(4, 'teacher4');


DROP TABLE IF EXISTS `teacher_course_assignment`;

CREATE TABLE IF NOT EXISTS `teacher_course_assignment` (

  `teacher_id` int(11) NOT NULL,

  `course_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;




INSERT INTO `teacher_course_assignment` (`teacher_id`, `course_id`) VALUES

(1, 1),

(1, 2),

(3, 2),

(1, 3),

(3, 4),

(4, 4);




ALTER TABLE `course`

 ADD PRIMARY KEY (`id`);


ALTER TABLE `teacher`

 ADD PRIMARY KEY (`id`);


ALTER TABLE `teacher_course_assignment`

 ADD PRIMARY KEY (`teacher_id`,`course_id`), ADD KEY `course_id` (`course_id`);


ALTER TABLE `course`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;


ALTER TABLE `teacher`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;


ALTER TABLE `teacher_course_assignment`

ADD CONSTRAINT `teacher_course_assignment_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`),

ADD CONSTRAINT `teacher_course_assignment_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`);
2

IMO, you should take #2 approach: add “price” column to teacher_course_assignment table, and create TeacherCourseAssignment model that has “price” property. It’s the simplest.

Currently, you can write like this with N:N relation defined:




foreach($teacher->courses as $course) {

    echo $course->coursename;

}

// Or

foreach($course->teachers as $teacher) {

    echo $teacher->teachername;

}

But N:N relation can be defined as a set of 2 1:N relations.

The code above can be written like the following with 2 1:N relations.




foreach($teacher->TCAs as $TCA) {

    echo $TCA->course->coursename;

}

// Or

foreach($course->TCAs as $TCA) {

    echo $TCA->teacher->teachername;

}

Just adding TCA->price will do all the tasks you need.




foreach($teacher->TCAs as $TCA) {

    echo $TCA->course->coursename;

    echo $TCA->price;

}

// Or

foreach($course->TCAs as $TCA) {

    echo $TCA->teacher->teachername;

    echo $TCA->price;

}
3

Thank you for your understandable answer. It works excellent. :D