AnsweredAssumed Answered

How many join tables needed?

Question asked by deathrobot on Aug 28, 2013
Latest reply on Jun 15, 2017 by philmodjunk


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?