Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2012
    Posts
    70

    Unanswered: Many-to-Many relationships

    I am fairly new to databases and am trying to learn.
    i am struggling with one thing at the moment.

    Lets think of a scenario:

    Lets say that I want to make a DB for a sports club.

    A lesson for the club is made up of instructors and students. However, depending on the lesson, the instructor in one lesson may be the student in another and visa versa (Not necessarily the best example to use - its all I can think of though).


    How can I setup my tables to show this?


    I'll need a "People" table to hold the information for the students/instructors and a "Lesson" table to essentially group it all together.


    However, how do I make it so that Each lesson can have many instructors and many students too. And also show that a person is a student in many lessons and an instructor in many (different) lessons too?



    Sorry if this is a basic question

  2. #2
    Join Date
    Aug 2012
    Posts
    70
    Example with test data:



    Lesson 1:
    Instructors - Dave, Jimmy, Kelly
    Students - Timmy, Bob, Diana, Beth

    Lesson 2:
    Instructors - Timmy, Diana
    Students - Hagrid, Harry

    Lesson 3:
    Instructors - Beth, Dave, Kelly, Hagrid
    Students - Jimmy, Harry, Diana

    Lesson 4:
    Instructors - Steve, Billy
    Students - Dave, Jimmy, Kelly, Harry, Diana, Timmy

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look up intersection / junction tables. in a realtional db thats the conventional way to model many to many realtionships
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2012
    Posts
    70
    I've used them before but am very rusty on them and don't know how to incorporate them then into the lesson table.


    In my LESSON table, do I have two field named INSTRUCTORS and STUDENTS and then, for the specific lesson, some sort of reference to the junction table?


    Would I make two junction tables between the PEOPLE table and the LESSONS table, one representing the relationships as a student and one as an instructor. Then somehow reference this in the LESSONS table?


    e.g.

    INSTRUCTORSLESSONS
    PK |PEOPLE_ID|LESSON_ID
    1 |P0001 |L0003
    2 |P0003 |L0003
    3 |P0002 |L0002
    4 |P0001 |L0002
    5 |P0001 |L0001



    STUDENTSLESSONS
    PK |PEOPLE_ID|LESSON_ID
    1 |P0001 |L0004
    2 |P0004 |L0003
    3 |P0005 |L0003
    4 |P0001 |L0007
    5 |P0001 |L0006

  5. #5
    Join Date
    Aug 2012
    Posts
    70
    I still don't see how I can specify that one of the tables would be students and the other instructors when using the same table of people.

  6. #6
    Join Date
    Aug 2012
    Posts
    70
    Do I need a structure like this?

    I know this is Access and I'll be using MySQL but this was the easiest way I could think of to represent it.


    I don't see how I will specify that one will be students and the other instructors still for each lesson.


    http://i.imgur.com/CM2ZaTl.png?1

  7. #7
    Join Date
    Aug 2012
    Posts
    70
    At the moment, the only thing I can think of it to duplicate the PEOPLE table and name each instance "STUDENTS" and "INSTRUCTORS". Then when linked to the LESSONS table, it will be obvious what each person listed is as part of the lesson as the fields will be along the lines of "Student_Name" and "Instructor_Name" whereas currently it would (Which would probably not work) "Name" and "Name".

    But, assuming everybody is a student and instructor at some point, these identical tables with merely different names creates redundancy and means that the details of each person cannot be updated all at once - obvious this would be a very bad DB practice.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though I might beside the point,
    I want to show an example.

    Code:
    CREATE TABLE test_m2m.Lesson
    ( Lesson_id   CHAR(05)    NOT NULL
    , Lesson_name VARCHAR(10) NOT NULL
    , PRIMARY KEY( Lesson_id )
    );
    
    CREATE TABLE test_m2m.People
    ( People_id   CHAR(05)    NOT NULL
    , People_name VARCHAR(10) NOT NULL
    , PRIMARY KEY( People_id )
    );
    
    
    CREATE TABLE test_m2m.Participant
    ( Lesson_id   CHAR(05)    NOT NULL
    , People_id   CHAR(05)    NOT NULL
    , role        VARCHAR(10) NOT NULL
                  CHECK( role IN ('student' , 'instructor') )
    , PRIMARY KEY( Lesson_id , People_id )
    , FOREIGN KEY( Lesson_id ) REFERENCES test_m2m.Lesson
    , FOREIGN KEY( People_id ) REFERENCES test_m2m.People
    );

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The sample data might be like...

    Code:
    INSERT INTO test_m2m.Lesson
    VALUES
      ( 'L0001' , 'Lesson 1' )
    , ( 'L0002' , 'Lesson 2' )
    , ( 'L0003' , 'Lesson 3' )
    , ( 'L0004' , 'Lesson 4' )
    ;
    
    INSERT INTO test_m2m.People
    VALUES
      ( 'P0001' , 'Dave'   )
    , ( 'P0002' , 'Jimmy'  )
    , ( 'P0003' , 'Kelly'  )
    , ( 'P0004' , 'Timmy'  )
    , ( 'P0005' , 'Bob'    )
    , ( 'P0006' , 'Diana'  )
    , ( 'P0007' , 'Beth'   )
    , ( 'P0008' , 'Hagrid' )
    , ( 'P0009' , 'Harry'  )
    , ( 'P0010' , 'Steve'  )
    , ( 'P0011' , 'Billy'  )
    ;

    Code:
    INSERT INTO test_m2m.Participant
      ( 'L0001' , 'P0001' , 'instructor' )
    , ( 'L0001' , 'P0002' , 'instructor' )
    , ( 'L0001' , 'P0003' , 'instructor' )
    , ( 'L0001' , 'P0004' , 'student'    )
    , ( 'L0001' , 'P0005' , 'student'    )
    , ( 'L0001' , 'P0006' , 'student'    )
    , ( 'L0001' , 'P0007' , 'student'    )
    , ( 'L0002' , 'P0004' , 'instructor' )
    , ( 'L0002' , 'P0006' , 'instructor' )
    , ( 'L0002' , 'P0008' , 'student'    )
    , ( 'L0002' , 'P0009' , 'student'    )
    , ( 'L0003' , 'P0007' , 'instructor' )
    , ( 'L0003' , 'P0001' , 'instructor' )
    , ( 'L0003' , 'P0003' , 'instructor' )
    , ( 'L0003' , 'P0008' , 'instructor' )
    , ( 'L0003' , 'P0002' , 'student'    )
    , ( 'L0003' , 'P0009' , 'student'    )
    , ( 'L0003' , 'P0006' , 'student'    )
    , ( 'L0004' , 'P0010' , 'instructor' )
    , ( 'L0004' , 'P0011' , 'instructor' )
    , ( 'L0004' , 'P0001' , 'student'    )
    , ( 'L0004' , 'P0002' , 'student'    )
    , ( 'L0004' , 'P0003' , 'student'    )
    , ( 'L0004' , 'P0009' , 'student'    )
    , ( 'L0004' , 'P0006' , 'student'    )
    , ( 'L0004' , 'P0004' , 'student'    )
    ;

  10. #10
    Join Date
    Aug 2012
    Posts
    70
    Quote Originally Posted by tonkuma View Post
    The sample data might be like...

    Code:
    INSERT INTO test_m2m.Lesson
    VALUES
      ( 'L0001' , 'Lesson 1' )
    , ( 'L0002' , 'Lesson 2' )
    , ( 'L0003' , 'Lesson 3' )
    , ( 'L0004' , 'Lesson 4' )
    ;
    
    INSERT INTO test_m2m.People
    VALUES
      ( 'P0001' , 'Dave'   )
    , ( 'P0002' , 'Jimmy'  )
    , ( 'P0003' , 'Kelly'  )
    , ( 'P0004' , 'Timmy'  )
    , ( 'P0005' , 'Bob'    )
    , ( 'P0006' , 'Diana'  )
    , ( 'P0007' , 'Beth'   )
    , ( 'P0008' , 'Hagrid' )
    , ( 'P0009' , 'Harry'  )
    , ( 'P0010' , 'Steve'  )
    , ( 'P0011' , 'Billy'  )
    ;

    Code:
    INSERT INTO test_m2m.Participant
      ( 'L0001' , 'P0001' , 'instructor' )
    , ( 'L0001' , 'P0002' , 'instructor' )
    , ( 'L0001' , 'P0003' , 'instructor' )
    , ( 'L0001' , 'P0004' , 'student'    )
    , ( 'L0001' , 'P0005' , 'student'    )
    , ( 'L0001' , 'P0006' , 'student'    )
    , ( 'L0001' , 'P0007' , 'student'    )
    , ( 'L0002' , 'P0004' , 'instructor' )
    , ( 'L0002' , 'P0006' , 'instructor' )
    , ( 'L0002' , 'P0008' , 'student'    )
    , ( 'L0002' , 'P0009' , 'student'    )
    , ( 'L0003' , 'P0007' , 'instructor' )
    , ( 'L0003' , 'P0001' , 'instructor' )
    , ( 'L0003' , 'P0003' , 'instructor' )
    , ( 'L0003' , 'P0008' , 'instructor' )
    , ( 'L0003' , 'P0002' , 'student'    )
    , ( 'L0003' , 'P0009' , 'student'    )
    , ( 'L0003' , 'P0006' , 'student'    )
    , ( 'L0004' , 'P0010' , 'instructor' )
    , ( 'L0004' , 'P0011' , 'instructor' )
    , ( 'L0004' , 'P0001' , 'student'    )
    , ( 'L0004' , 'P0002' , 'student'    )
    , ( 'L0004' , 'P0003' , 'student'    )
    , ( 'L0004' , 'P0009' , 'student'    )
    , ( 'L0004' , 'P0006' , 'student'    )
    , ( 'L0004' , 'P0004' , 'student'    )
    ;

    Thanks.

    Can I just check that I understand what that will do? (I'm very new to MySQL so do not understand it much).

    It creates 3 tables; Lesson, People and Participant
    Lesson has 2 attributes; Lesson_id and Lesson_name
    People has 2 attributes; People_id and People_name
    Participants has 3 attributes; Lesson_id, People_id and role

    In participants, the primary key is a composite primary key made from lesson_id and people_id which are both foreign keys referencing the other two tables. The role attribute is plain text which is restricted to either "Student" or "Instructor" because of the CHECK function which disallows the condition from ever being false (I have only just learnt of this function, is this correct?)

    Participants is therefore a junction table allowing the many-to-many relationship between Lesson and People and it assigns the person as either a Student or Instructor through the role attribute's value.

    Therefore, when actually using the DB, it will query the value of participant.role ("WHERE role = [Instructor/Student]" in a MySQL query) in order to output the actual relationship to the real-world lesson.



    Is this correct?

    Also, just a quick question too, by convention would "Participants" usually be named LessonPeople? This is what I've seen with junction tables in the past.



    Thank you so much for the effort you have put in.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Also, just a quick question too, by convention would "Participants" usually be named LessonPeople? This is what I've seen with junction tables in the past.
    It is possible to have more than two many-to-many relationships between a pair of two tables.
    Though the cases might be not so many.

  12. #12
    Join Date
    Aug 2012
    Posts
    70
    I used this:

    Code:
    PRIMARY KEY( Lesson_id , People_id )
    , FOREIGN KEY( Lesson_id ) REFERENCES test_m2m.Lesson
    , FOREIGN KEY( People_id ) REFERENCES test_m2m.People
    But when I use
    Code:
    DESCRIBE test_m2m.Participant;
    The "key" table only shows Lesson and People as PRI. There is no mention of foreign keys :/


    Why is this?

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though, I know a little about MySQL,
    many DBMS make physical index automatically for PRIMARY KEY, mainly to guarantee uniqueness.

    But, such physical index is not necessary for FOREIGN KEY.
    Because, FOREIGN KEY instructs DBMS to check existence of the key value in the key of the table specified by REFERENCES clause.

    However, creating a physical index on FOREIGN KEY might be (sometimes) useful for performance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •