I'm having difficulty linking some tables together. Basically the database is being hosted on MS SQL, but is still in design stage so any alterations can still be made. I need to link the following tables:

Students - This table holds information about the students attending a college. Each student is given a unique identifier.

Staff - Holds information about the staff, contains a unique identifier for each staff member

Assignments - Holds information about assignments, has unique identifier for each assignment.

Courses - Holds information about courses, has unique identifier for each course.

Progress - This holds information about each student's progress of each assignment. This is also used by the staff to mark assignments, and used by the college intranet to display to students which assignments are currently unfinished.

Now, many students can study on many courses. Many staff can teach on many courses. Each course contains many assignments.

Course=---=Students
Course=---=Staff
Course----=Assignments

Which is easy enough. However, the database is going to be used to create a more intricate information system, as part of mainly an ASP intranet system. So we need to some how add the progress table to the existing design so we can pull the following information from the database easily...

So from the database we need to:

• display new assignments for students (if any) that staff submit
• find out which course the student is studying, so we can direct the student to appropriate information
• display student names for a staff member for individual courses that that they study on, as well as all courses
• and also some other minor queries, but these are not majorly important.

If someone could suggest how we would go about relating the tables together to provide a solution that we can easily query and get the information above, we would really appreciate it, because we are stumped

Thanks in advance,

Daniel Thompson

Hi,

I think that you should have:

TABLES:

- students (student_id, ....)
- staffs (staff_id,....)
- courses (course_id, ...)
- assignments(assign_id,..., course_id): course_id is a fk

- student_courses (student_id, course_id)
- staff_courses (staff_id, course_id, ....)

- progess(student_id, assign_id, mark)

RELATIONSHIPS: have a look at the relational diagram below:

- students --1----n---->student_cousers
- courses --1----n---->student_cousers
- staffs --1----n---->staff_cousers
- courses --1----n---->staff_cousers
- student ---1---n----> progress
- assignments --1----n----> progress
- assignments --1----n----> cousers

You can easily use the query feature to get the information from related tables above.

Hope that helps.

TheStone.

B.