Mysql – this type of query called, and how can I formulate it

You want three tables for this:

• Users with a row per user and whatever details you need
• Groups with a row per group and whatever details you need
• UsersGroups with a unique combination of UserId, GroupId that only keeps track of relationships

This will let you have as many combinations as you like.

Something like this:

Party
id
type: {individual, group}
name

PartyRelationship
fromPartyId
toPartyId
type: {groupMembership}    

Event
id
name
scheduledStart
scheduledEnd
actualStart
actualEnd

EventRole
eventId
roleType {organizer, scheduler, participant}
partyId

List
id
eventId
name

ListItem
id
listId
description
createdBy /* should point to a party */

An Event hasMany Roles, which could be organizers, participants, etc.

A participant can be either an individual or group, both of which are subtypes of Party

A Group party has many Individual parties, as described in the PartyRelationship table. An Individual can belong to zero+ groups.

An Event hasMany Lists, and a List hasMany Items. A list belongsTo one Event, and a ListItem belongsTo one List.

Example usage:

insert into party values (1, 'individual', 'neil');
insert into party values (2, 'individual', 'bernk');
insert into party values (3, 'individual', 'simon');
insert into party values (4, 'individual', 'ypercube');

insert into party values (5, 'group', 'canadians');

insert into partyRelationships values (5, 1);
insert into partyRelationships values (5, 2);
/* now neil and bernk are related to the 'canadians' party */

insert into event values (1, 'an event with canadians and ypercube, organized by simon', '2012-07-01', '2012-07-02');

insert into eventRoles values (1, 'organizer', 3); /* simon is the organizer*/
insert into eventRoles values (1, 'participant', 5); /* the 'canadians' group is a participant */
insert into eventRoles values (1, 'participant', 4) /* ypercube is a participant too */