asked on

Designing a database

I am designing a database to hold information about students and projects within a school.
A student may work on any number of projects, and multiple students may work on the same project.
Each project will have at least one student designated as the project leader.
Multiple co-leaders on the same project are allowed.
Similarly, each project will have at least one project sponsor (again, possibly more than one).
Finally, each project may have additional assigned students (with no specific role).

What tables would you propose for this system, and how would they relate to one another?

Scott Pletcher

Here's a quick draft of the major entities and keys.

I've added a school entity just to allow for the possibility of later merging project dbs from multiple schools into a single db. For your single school, the school_id will always be the same.

Note that the school_id is deliberately not in the project_students and project_sponsors entity, so that students from diff schools could theoretically work together on the same project. In this design, if a project is worked on by multiple schools, one school must be designated as the owner/controller of the project.

schools
school_id

sponsors
school_id
sponsor_id

students
school_id
student_id

projects
project_id
owning_school_id

project_students
project_id
student_id
is_leader

project_sponsors
project_id
sponsor_id

zachvaldez

ASKER

Is the project leader identified as a student ?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zachvaldez

ASKER

One constraint is that only limited in 1 school. Only students from that same school can be co leaders for the project .