Consider using inheritance to model the various kinds or artists:
(BTW, your Group_Member table allows groups-in-groups. I'm guessing this is not what you wanted.)
However, this ignores any differences roles might have depending on the artist type. For example, it makes little sense for a whole group to be a "Conductor". If enforcing these kinds of constraints is important, you could take a "brute force" approach and simply separate the person-specific from group-specific roles:
(BTW, if you wanted to prevent overlap between names of group-specific and person-specific roles, you'd have to put both role tables in an inheritance hierarchy. Not shown here.)
Of course this still doesn't honor the cardinality of certain roles. For example, only one person (per performance) can be the "Conductor". To solve that, you'd have to extend the model even further:
And possibly similar thing would have to be done for the groups.
To get all the people involved in a given performance (say 27) through any of these three kinds of roles, you'd need a query similar to this:
SELECT *
FROM Person
WHERE person_id IN (
SELECT person_id
FROM Group_Person JOIN Group_Performance_Role
ON Group_Person.group_id = Group_Performance_Role.group_id
WHERE performance_id = 27
UNION
SELECT person_id
FROM Person_Performance_MultiRole
WHERE performance_id = 27
UNION
SELECT person_id
FROM Person_Performance_SingleRole
WHERE performance_id = 27
)
Note hat this lists people at most once, even when they are involved with the performance in multiple roles (e.g. a same person can be a "Conductor" and a member of a group that has a role on the same performance).
To also get their role names, you could:
SELECT Person.*, group_role_name
FROM Person
JOIN Group_Person
ON Person.person_id = Group_Person.person_id
JOIN Group_Performance_Role
ON Group_Person.group_id = Group_Performance_Role.group_id
WHERE performance_id = 27
UNION ALL
SELECT Person.*, person_multirole_name
FROM Person
JOIN Person_Performance_MultiRole
ON Person.person_id = Person_Performance_MultiRole.person_id
WHERE performance_id = 27
UNION ALL
SELECT Person.*, person_singlerole_name
FROM Person
JOIN Person_Performance_SingleRole
ON Person.person_id = Person_Performance_SingleRole.person_id
WHERE performance_id = 27
As you can see, we keep making the model more and more precise, but also more and more complex. And we haven't even gone into songs and albums, and evolving group members (etc...) yet. I guess the onus is on you to decide where is the right balance between "precision" and simplicity.
