How many join tables needed?
I am setting up a project management database and as part of this, have a People table and a Projects table. In the People table, each person can be any combination of "Client", "Musician", or "Staff" (ocassionally someone fits into more than one category). On the Project layout, I need to be able to attach people from these various categories (I would have a 3-tabbed panel - one for each category - and each tab would have a portal showing either clients, musicians, or staff). I thought the best way to do this would be with 3 different join tables like this:
Projects ---< TeamClients >--- People
Projects ---< TeamMusicians >--- People
Projects ---< TeamStaff >--- People
That way, a person could be, for example, both staff and musician. That's all fine. But now on the People layout, if I want to show all projects a person was attached to no matter what category they're from and no matter what role they played, having 3 join tables complicates things. It would be better to have just one:
Projects ---< Team >--- People
Is there a straightforward way to rectify these conflicting needs?