Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
A few hundred thousands rows in a table is not gigantic. Not in today's hardware and software. –  ypercube Jul 1 '12 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. –  Simon Righarts Jul 1 '12 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 '12 at 21:23
    
@SimonRigharts can you please elaborate on indexing properly? –  bernk Jul 1 '12 at 21:24
    
A billion+ is gigantic these days –  Neil McGuigan Jul 1 '12 at 21:24
show 11 more comments

1 Answer

up vote 2 down vote accepted

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 */
share|improve this answer
    
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 '12 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. –  Neil McGuigan Jul 1 '12 at 21:58
    
Great answer. I appreciate all the effort that went into it very much. –  bernk Jul 1 '12 at 22:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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