M:N relationship

[rasi]

Could you explain some tricks, how to make subforms with M:N relationships tables?

[massiws]

rasi, have a look at this post.

Max

[rasi]

Thank you massiws, for answer.
I have new question. My example tables:

Code: Select all

CREATE TABLE IF NOT EXISTS `student` (
  `id_student` varchar(15) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id_student`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `teacher` (
  `id_teacher` varchar(15) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id_teacher`)
) ENGINE=MyISAM

CREATE TABLE IF NOT EXISTS `student_has_teacher` (
  `student_id_student` varchar(15) NOT NULL,
  `teacher_id_teacher` varchar(15) NOT NULL,
  PRIMARY KEY (`student_id_student`,`teacher_id_teacher`),
  KEY `fk_student_has_teacher_teacher1_idx` (`teacher_id_teacher`),
  KEY `fk_student_has_teacher_student1_idx` (`student_id_student`)
) ENGINE=MyISAM;

When I try to create subform via subform wizzard, wizzard not show tablefields for table student_has_teacher. Why?
It is important for me, that combination student-teacher must be unique.

[massiws]

rasi,
you made some mistakes:

• in nuBuilderPro table IDs must be defined VARCHAR(25);
• student_has_teacher table must have an ID field, so you can get unique records;

You should have:
- student
-- student_id -> VARCHAR(25)
-- stu_name -> VARCHAR(45)

- teacher
-- teacher_id -> VARCHAR(25)
-- tea_name -> VARCHAR(45)

- student_has_teacher
-- sht_id -> VARCHAR(25)
-- sht_student_id -> VARCHAR(25)
-- sht_teacher_id -> VARCHAR(25)

Max