If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
many-to-many relationship
I researched to figure how and when to create a m:m relationship. I'm building a database that will have classes and students in it. I have a table named class and one named stupersonal. One student may have several classes and one class may have many students, so I made a third table named StudentClass. All the table has as of now are two PKIDs which are the same as the ones from each of the other tables. I have a one-to-many relationship from Student ID to Class ID in StudentClass and I have a one-to-many relationship from Class ID to Student ID in StudentClass. First of all, I want to know if I've set it up right? Do I need to add all the fields from my two tables to the junction table? When designing forms and writing reports; will have to use the junction table as the source? I'm trying to understand in plain terms, how the junction table acts in this relationship. I am fairly new to this and seem to be "stuck" with this obstacle. Any help is greatly appreciated in advance!
|
#2
|
|||
|
|||
You have done the right thing! That's exactly how you should tackle
this. No, you don't need any other field from the classes and student tables in the StudentClass one(you could call it Enrollments, if it makes more sense). You could have other fields there, though, relating to a each particular enrollment, such as year, semester, major/minor, grade etc. HTH, Nikos TSG wrote: I researched to figure how and when to create a m:m relationship. I'm building a database that will have classes and students in it. I have a table named class and one named stupersonal. One student may have several classes and one class may have many students, so I made a third table named StudentClass. All the table has as of now are two PKIDs which are the same as the ones from each of the other tables. I have a one-to-many relationship from Student ID to Class ID in StudentClass and I have a one-to-many relationship from Class ID to Student ID in StudentClass. First of all, I want to know if I've set it up right? Do I need to add all the fields from my two tables to the junction table? When designing forms and writing reports; will have to use the junction table as the source? I'm trying to understand in plain terms, how the junction table acts in this relationship. I am fairly new to this and seem to be "stuck" with this obstacle. Any help is greatly appreciated in advance! |
#3
|
|||
|
|||
In addition to Nikos' comments, I'll add that you absolutely do NOT want to
duplicate data in your "junction"/"relation"/"resolver" table. When it comes time to display (query, form, report) the details underlying "Enrollment", use a query that joins all three (student, class, enrollment) tables together. This way, you only need to record a fact one time, not over and over again. -- Good luck Jeff Boyce Access MVP "TSG" wrote in message ... I researched to figure how and when to create a m:m relationship. I'm building a database that will have classes and students in it. I have a table named class and one named stupersonal. One student may have several classes and one class may have many students, so I made a third table named StudentClass. All the table has as of now are two PKIDs which are the same as the ones from each of the other tables. I have a one-to-many relationship from Student ID to Class ID in StudentClass and I have a one-to-many relationship from Class ID to Student ID in StudentClass. First of all, I want to know if I've set it up right? Do I need to add all the fields from my two tables to the junction table? When designing forms and writing reports; will have to use the junction table as the source? I'm trying to understand in plain terms, how the junction table acts in this relationship. I am fairly new to this and seem to be "stuck" with this obstacle. Any help is greatly appreciated in advance! -- TSG |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Database Shapes - Entity Relationship (US units) - Can't anchor Relationship shape on Entity shape | [email protected] | Visio | 1 | March 28th, 2005 04:47 AM |
Deleting a foreign key relationship in SQL | Stevio | Running & Setting Up Queries | 2 | December 22nd, 2004 02:51 PM |
Re-establishing a broken relationship | David McKnight | Database Design | 2 | December 1st, 2004 10:49 AM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Setting dual relationship with tool connector | Carlos | Visio | 0 | May 20th, 2004 12:51 AM |