I'm designing a database to track research proposal review and I'm having trouble sorting out my design structure. I've watched lots of tutorials, but I'm still having trouble setting up the right tables and relationships.
My database will contain information on research projects (project title, start date, status, approval date, etc.) and people (first name, last name, campus, etc.). There are different roles involved with each research project (investigator, advisor, primary reviewer, secondary reviewer). Each project can have multiple people attached to it. For example, a project can have two investigators, an advisor, and a primary reviewer. Investigators can have multiple projects going and a person could have multiple roles (i.e. they could be an advisor and an investigator), but each person can only have one role per project.
Originally I thought I would need the following tables: project (PK ProjectID, fK AssignmentID), people (PK PersonID), project role (PK RoleID), project assignment (PK Assignment ID, fK PersonID, fK RoleID). But this doesn't seem quite right. Any help is greatly appreciated!