database design -> Class, level, topics. marks -


i'm designing database school. far have following tables:

 course:        topic:        level         leveltopic_junc:  student:   courseid (pk)  topicid (pk)  levelid (pk)  levelid (fk)      studentid         date           topicname     levelname     topicid (fk)      studentname       level(fk)   coursestudent_junc:   courseid (fk)  studentid(fk)  topicid (fk)  mark 

now i'm @ dead point. need is: every student, @ end of course pass exam , gets mark every topic of course , final mark average of previuos marks.

also, leveltopic_junc can change: instance, year topic1 can in level1, next year topic1 in level2. shouldn't change topics assigned edit:courses previous years. if courseid:564 (level1) year has topic1, when next year modify leveltopic_junc table, courseid:564 should maintain topic1, if level1 it's not anymore in level1. leveltopic_junc table it's reference table (later, when writing php) can populate coursestudent_junc table topics, without have manually search or write topics.

would better break coursestudent_junc in 2 tables (or break 1 of normalization rules?)

coursetopic_junc:     mark: courseid (fk)        courseid (fk) topicid (fk)         studentid (fk)                      topicid (fk)                      mark 

what best database design in scenario?

hope clear, isn't easy explain in words :)! thanks!!


Comments