[thelist] Newbie - DB Design Help

Ken Moore psm2713 at hotmail.com
Sat Jul 29 16:55:20 CDT 2006

Hi all,

Rob Smith laid out the schema below for a M:M relationship. He is exactly 
right. For example we want to set up a relationship between all students and 
all classrooms in which they have a class. Or, conversely, we want to set up 
a relationship between all classrooms and all students that have a class in 
it.. Each student is related to many classrooms and each classroom is 
related to many students.

At first look, this appears to be a very complicated job. What you do is to 
set up a "student_class_table" with a field for the classroom and another 
for the student. This is the only manageable way that database designers 
have to add all relationships between any possible classroom and any 
possible student.

Add one record for each student-classroom that you have. You might add more 
info that is unique to the relationship such as time, teacher, subject. You 
would need multiple entries if the same student had the same classroom more 
than once. Ditto if yu need to track specific days (MWF or TTh).

Then you simply which one of the elements you are processing. Do you want to 
print out the students schedule and classroom? Do you need to find out which 
classrom has the fewest students so you can use the space better?

The theory is that that a student-classroom relationship is a unique bit of 
information that may be helpful if it is stored. Yes, it is a bit of info 
that can retreived otherwise from student and classroom tables. But if this 
is something that is often needed, this is the way to do so.



><snip>Generally your entities will become tables. Where entities have a
>M:N (Many:Many) relationship, you will need a "bridging" table. If you
>do up your ER model first, your database schema (at least in 3rd normal
>form) writes itself.</snip>
>Yup. I agree that you disagree with me. You actually answered my next
>post about the best way to rethink this approach:
>                       Bridge_Dept_Prod
>    Deptarments      ---------------------
>----------------    bid         int 4  PK          Products
>dept_id int 4 PK => dept_id     int 4  FK    -------------------
>...                 product_id  int 4  FK <= product_id int 4 PK
>                                              ...
>Yes, the bad and the good news here is that you get to effectively
>micromanage the relationships in order to have a many-to-many
>Rob Smith

Express yourself instantly with MSN Messenger! Download today - it's FREE! 

More information about the thelist mailing list