How to improve this design

database-designrelational-theory

I'm creating an event planning PHP/MySQL web application as a learning project. The whole app revolves around the idea of the event and its various components (participants, groups, lists, etc.)

I understand how to use PHP and MySQL but my theoretical knowledge is at a basic level. I have setup and used very simple MySQL databases before, with little or no relationships between tables. This project is quite different.

Let me explain my idea of lists in the context of an event. The initiator of an event can create any number of lists for that event, each list can have items added to it by any participant of the event. With my current knowledge I would create the following tables: events, users, eventuser, lists, eventlist, items, listitem, etc.

This doesn't feel right… If the app was to be used by a couple thousand users, each making a few events, each having a couple of lists, each having a few list items… well, you get the idea. The listitem table would get gigantic pretty quick. Am I totally wrong? Would that be OK? Would it be better to create a set of tables for each event? …that doesn't feel right either.

What is the right way to do this? Or at least a better way?

Best Answer

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 */
Related Question