View Single Post
Old 05-28-2019, 02:31 PM   #12
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,843
Thanks: 56
Thanked 1,046 Times in 1,008 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Need help with table relationships!

Just for info, the big thing is the junction table.

You can only model a 1-many relationship (1-1 is a special sort of 1-many)

if you have a many to many relationship then you have to devolve it into two 1-many relationships

So if you have tables for Students and Courses, you necessarily have students doing multiple courses, and courses with multiple students

So you need a third table called student-courses

and your relationships become
Student - 1 to many - StudentCourses - many to 1 Courses

This is similar to the theDBGuys junction table tblStudent-Programs - which joins the Student-Demographics table to the Programs table via the junction table.

A junction table often doesn't have many other fields, but in this case does. The student-programme record in the junction table should be a unique entry (the same student should only do the program once) - so it is convenmient to store info about this allocation in the junction table. If you need courses to be repeated ever, then the model might need to change to reflect that.

(I presume the scrolled off fields at the top of the Student Demographics include the student names and address and personal info, etc, as well as a lot more beside.)

--------------------
when you decide to store a new bit of data, you either already have a logical table in which it should go, or you need a new table. In that case you need to decide how the new table needs to relate to other tables in your database, whicle maintaining 1 to many relationships.

eg - if you wanted to record staff members responsible for the various student assessments.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote