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

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


More information about the thelist mailing list