[thelist] Newbie - DB Design Help

[Ken Moore]

Hi all,

This is getting beyond the original question. But for the sake of being 
clear, we are not talking about right and wrong but about balancing good 
style with everyday restrictions.

First, data integrity problems arise from duplicate entries of the same data 
and/or improper updating of data.

>From Ken Schaefer:
>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.

Of course teachers, classrooms, and students all have their own tables and 
would be recorded as foreign keys. The following is all that is needed to 
relate students and classrooms:

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

The table description that Ken Schaefer gave (below) certainly answers the 
original question and then some. The schema that he gives addresses the 
extra – and, it appears the complicating –  factors that I introduced about 
storing extra information.

>TABLE Classes
>	- ClassID
>	- SubjectID (FK)
>	- ClassroomID (FK)
>	- TeacherID (FK)
>	- Time
>
>To make this easier to visualize, I've done up a schematic:
>http://www.adopenstatic.com/temp/drawing1.jpg

The above is good enough for any textbook.


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

This depends on the relative costs. Often times, real-world constraints 
interfere with database design theory. I said before that all of this could 
be assembled elsewhere. With unlimited time, money, HW, programming talent, 
and management approval, that is the way to go.

Let me know if you if you are aware of such a place. I know of a very good 
database programmer who would be interested :-).

Ken

_________________________________________________________________
Don’t just search. Find. Check out the new MSN Search! 
http://search.msn.click-url.com/go/onm00200636ave/direct/01/