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?

  • 1
    A few hundred thousands rows in a table is not gigantic. Not in today's hardware and software. Jul 1, 2012 at 21:19
  • 2
    Creating a different table for each event is a bad idea. Dynamic DDL is an administration disaster. Also echoing ypercube, a few hundred thousand rows isn't gigantic, particularly for narrow tables (ones without many fields). Just make sure you index them properly. Jul 1, 2012 at 21:21
  • @ypercube Wow, all right… so what IS considered gigantic these days? Or, what can I reasonably expect out a standard MySQL server setup? Thanks for the suggestion of reposting as a separate question : )
    – bernk
    Jul 1, 2012 at 21:23
  • @SimonRigharts can you please elaborate on indexing properly?
    – bernk
    Jul 1, 2012 at 21:24
  • A billion+ is gigantic these days Jul 1, 2012 at 21:24

1 Answer 1


Something like this:

type: {individual, group}

type: {groupMembership}    


roleType {organizer, scheduler, participant}


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 */
  • Thank you. I don't understand the fromPartyId and toPartyId fields in the PartyRelationship table, but the rest makes sense! Is it right to say then that the relationship between a List and ListItems is one-to-many?
    – bernk
    Jul 1, 2012 at 21:46
  • the Party system lets you point an eventRole to either an individual or a group. The partyRelationship table lists the group that a party belongs to. Yes, a list has many items, and an item belongs to one list. Jul 1, 2012 at 21:58
  • Great answer. I appreciate all the effort that went into it very much.
    – bernk
    Jul 1, 2012 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.