Okay, so this may get a bit long and confusing, but I am at a total loss. I have been creating a database for my job for the past couple of months.
This database tracks people, the departments, funders, and proposals. A proposal can only have one funder (I have this relationship perfected, so it's not a concern). A person can be in multiple departments and a department can have multiple people within it (I have solved this many-to-many relationship using a join table) The issue I am currently having is connecting these two to the proposal table. Within a the proposal table I have different fields that would track individuals, such as there is a person who is the primary contact (field) and then there would need to be a field that I can list and relate multiple individuals to, for they would be the other contacts linked to the proposal. For each person, I would need to select the department they are associated with that applies to that proposal, and if possible have a way to pick multiple departments and not just one. My ultimate question, is how do I link these tables so I have a many-to-many relationship between people and proposal, as well as a many-to-many relationship between proposals and departments.
I'm sorry if this does not make much sense! It's a confusing and complex situation and each time I try to explain it to either myself or someone else I get more confused. I can try to answer any questions that may arise that can help clarify my situation.