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?
zachvaldezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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
zachvaldezAuthor Commented:
Is the project leader identified as a student ?
Scott PletcherSenior DBACommented:
There's a separate "project_students" table to identity which student(s) are assigned to project.

Within that table, there's a flag that identifies whether that student is a project leader on that project or not.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

zachvaldezAuthor Commented:
One constraint is that only limited in 1 school. Only students from that same school can be co leaders for the project .
Dung DinhDBA and Business Intelligence DeveloperCommented:
You need to understand some fundamentals about Enity-Relationship(ER) diagram and Relational diagram. Normally, we start designing ER diagram first and then convert ER diagram to Relational diagram. With your requirement, there are three main entities
  1. Project
  2. Student
  3. Sponsor


Now we define the relationship  for these entities
- A project must have a student as a leader of the project or a project can have many students to work on it.
- A student can work on one or many project or No project
==> The relationship among Project and Student is n-n and named WORK

- A project must have a sponsor or a project can have many sponsors.
- A sponsor can sponsor one or many project or No project
==> The relationship among Project and Sponsor is n-n and name SPONSOR. A relationship has also attribute and in case of WORK relationship, I add an attribute Leader to identify who is leader of a project.
ER Diagram
ER.png
Now we convert the ER diagram to Relational Diagram
If the relationship is n-n, we will create a new table with key is combination of keys of two entities
Relational.png
zachvaldezAuthor Commented:
Thank you for your generosity. It helped me a lot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.