sql - Modelling Music Artist/Group -

i building web app displays charting records/albums 50s , 60s.

currently have (lots of irrelevant stuff removed):

the table named "group" once modeled "person" table, "person_group" joining table , "group" table. problem can't have foreign key 'group_performance_role.group_id' reference both "person" , "group" tables.

i decided keep structure require every "person" automatically "group" of 1; however, creates naming ambiguity , means table holds more 1 thing - single artist ("john lennon") , group ("the beatles").

i need find way link artist (which single person or group of people) performance, while avoiding bad practices table holds more 1 "type" of thing.

i have searched web, found little in way of relevant answers. help/advice/suggestions appreciated!!

edit: "role" table lookup table roles people/groups can perform on performances. examples: "artist", "composer", "orchestra conductor" etc etc

consider using inheritance model various kinds or artists:

(btw, group_member table allows groups-in-groups. i'm guessing not wanted.)

however, ignores differences roles might have depending on artist type. example, makes little sense whole group "conductor". if enforcing these kinds of constraints important, take "brute force" approach , separate person-specific group-specific roles:

(btw, if wanted prevent overlap between names of group-specific , person-specific roles, you'd have put both role tables in inheritance hierarchy. not shown here.)

of course still doesn't honor cardinality of roles. example, 1 person (per performance) can "conductor". solve that, you'd have extend model further:

and possibly similar thing have done groups.

to people involved in given performance (say 27) through of these 3 kinds of roles, you'd need query similar this:

select * person person_id in (      select person_id     group_person join group_performance_role         on group_person.group_id = group_performance_role.group_id     performance_id = 27      union      select person_id     person_performance_multirole     performance_id = 27      union      select person_id     person_performance_singlerole     performance_id = 27  ) 

note hat lists people @ once, when involved performance in multiple roles (e.g. same person can "conductor" , member of group has role on same performance).

to also role names, could:

select person.*, group_role_name 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 performance_id = 27  union  select person.*, person_multirole_name person     join person_performance_multirole         on person.person_id = person_performance_multirole.person_id performance_id = 27  union  select person.*, person_singlerole_name person     join person_performance_singlerole         on person.person_id = person_performance_singlerole.person_id performance_id = 27 

---

as can see, keep making model more , more precise, more , more complex. , haven't gone songs , albums, , evolving group members (etc...) yet. guess onus on decide right balance between "precision" , simplicity.