[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.
HTH,
Ken
>
><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
>relationship.
>
>Rob Smith
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
More information about the thelist
mailing list