[thelist] Newbie - DB Design Help

Ken Schaefer Ken at adOpenStatic.com
Sat Jul 29 23:09:41 CDT 2006


: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Ken Moore
: Subject: Re: [thelist] Newbie - DB Design Help
: 
: Rob Smith laid out the schema below for a M:M relationship.

It contains an unnecessary field. That can lead to data integrity problems.

: 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.

Correct, but you do not need a separate surrogate ID field
 
: 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.

These should be stored in a separate table(s). Teachers are a separate
entity. So are subjects. Times are an attribute of the class, so would be
fields in the Class table.

TABLE Teachers
	- TeacherID
	- TeacherName

TABLE Classrooms
	- ClassroomID
	- Location

TABLE Subjects
	- SubjectID
	- SubjectName

TABLE Students
	- StudentID
	- StudentName

TABLE Classes
	- ClassID
	- SubjectID (FK)
	- ClassroomID (FK)
	- TeacherID (FK)
	- Time

You would then need some bridging tables:
- Teachers teach one or more subjects (TeachersSubjects)
- Students would enroll in one or more Classes (StudentsClasses)

To make this easier to visualize, I've done up a schematic:
http://www.adopenstatic.com/temp/drawing1.jpg


: 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).

Surely students would be allocated to *Classes*, and Classes would be taught
in *Classrooms*, and would involve a *Subject*

This is where your design is starting to break down, because you are trying
to overload one table with too many separate entities.

Cheers
Ken



More information about the thelist mailing list