Database Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesDatabase Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old March 14th, 2010, 01:19 PM
PenguinDB PenguinDB is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2010
Posts: 1 PenguinDB User rank is Just a Lowly Private (1 - 20 Reputation Level) 
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.

tblStudent
Student_ID
LastName
FirstName
Email
Address
City
State
Zip
Country
Phone
UserName
Password

tblAuthor
Author_ID
...same as tblStudent except for UserName & Password

tblSchool
School_ID
schName
Address
City
State
Zip
Country
ContactName
ContactEmail

tblProfessor
Professor_ID
...same as tblStudent

tblCourse
Course_ID
Title
SubjectArea

Above are the main tables. I also think I should have some other seperate tables to support the relationships I need.

tblChapters
Course_ID
Chapter_ID
Title

tblAssignments
Chapter_ID
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)
Percent (score)
Grade (score translated into letter grade if necessary)

tblSubjectArea
SubjectArea_ID
AreaName

Then I need some linking tables (for many to many)

tblStudentSchool
Student_ID
School_ID

tblStudentCourse
Course_ID
Student_ID
Percent
Grade

tblSchoolCourse
School_ID
Course_ID

tblSchoolProfessor
School_ID
Professor_ID

tblProfessorCourse
Course_ID
Professor_ID

tblCourseAuthor
Course_ID
Author_ID

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,

Melinda

Last edited by PenguinDB : March 14th, 2010 at 02:33 PM. Reason: Table Mistake

Reply With Quote
  #2  
Old March 19th, 2010, 11:31 AM
AlanSidman's Avatar
AlanSidman AlanSidman is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2008
Location: Lake County, IL
Posts: 240 AlanSidman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 15 h 49 m 19 sec
Reputation Power: 10
Penguin--Left you a PM.

Alan
__________________
Alan Sidman
__________
If I helped you, I'd like to know. Click on the icon next to the thread number and tell me.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesDatabase Development > Help with Table Design & relationships


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap