March 14th, 2010, 02:19 PM
Join Date: Mar 2010
Time spent in forums: 24 m 24 sec
Reputation Power: 0
Help with Table Design & relationships
I am working on a self-picked project at school to deliver courses online. I hope to move this project beyond school later.
Here is the basic idea of what I envision. Students sign-up for membership, then choose the course or courses they want to take. Each course will be split up into chapters. There could be any number of assignments for each chapter--0 to some finite number (probably less than 5 and usually only 1).
The student may or may not be affiliated with a school. If with a school I will need to be able to record and retrieve the information based on the school.
The student may or may not have this course required from a professor. If required by the professor, then I will need to be able to record and retrieve the information based on the professor.
Every course will have been produced by an author.
Here are the tables.
...same as tblStudent except for UserName & Password
...same as tblStudent
Above are the main tables. I also think I should have some other seperate tables to support the relationships I need.
AssignmentNum (is this the 1st, 2nd,... for the chapter)
AssignPath (path to the location of the assignment)
AssignDate (Date the Assignment was issued)
CompDate (Date completed)
Grade (score translated into letter grade if necessary)
Then I need some linking tables (for many to many)
I am confused about the Assignment, Chapters and Cousre table relationships. Or even if these tables are the correct way to do what I need.
Every course will have at least one chapter, and may or may not have 1 or more assignments. The student needs to be able to view the chapters and the assignments. I want the student to see when the assignments were assigned, completed, and if graded, the score and/or grade. The professor should be able to view the students taking the course, the assignments assigned/unassigned, not completed, completed, those that are scored/not scored and the scores and/or grades of those assignments scored.
I envision a many to one relationship between the Chapters table and the Course table through the Course_ID field. I also see a many to one relationship between the Assignments table and the Chapters table through the Chapter_ID field. Do I need a seperate Assignment_ID field from the AssignNum field? The AssignNum field just stores a 1,2,3... etc. for each chapter in a particular course.
I would really appreciate some help as I am swimming in a relationship and table storm here (for me).
I have everything diagramed out, but I don't know how to get that on here.
Thanks for any and all help,
Last edited by PenguinDB : March 14th, 2010 at 03:33 PM.
Reason: Table Mistake