I've been struggling with this model for weeks, going in circles from one type of solution to another. I'd like some advice on the best approach.

One or more organizations can sponsor an event or project; projects are a series of events. Not all events are part of projects but projects always have events. Leaders of any of the sponsoring organizations can create and update an event or a project (and the events in that project). A project has leaders, who can also create and update events for that project. Events may have leaders who can also update that event. Does this make for too little accountability and too many people who can edit and add organizations, events, and projects?

For the output end, I need to display for the leader, each event, project, and organization they can update and then when they do update, make sure they have the right to update that particular event, project, or organization.

As I see it I have to have Event, Project, Organization, and Leader tables. I also need to store which organizations sponsor which events and which projects. Here is where I go round and round. Is it better to have two tables or one:

  1. two tables:
    • project-sponsor table (ID, organization_id, project_id) AND
    • event-sponsor table (ID, organization_id, event_id)
  2. one table with entity_type = 'project', 'event', or 'org':
    • sponsor (ID, organization_id, entity_type, entity_id) OR
    • sponsor (ID, organization_id, event_id, project_id)

I also need to store leaders and their roles in organizations, projects, and events. I face the same quandary. Is it better to have three tables or one:

  1. three tables:
    • org_leader(ID, leader_id, organization_id, role, title, term...) AND
    • project_leader(ID, leader_id, organization_id, role, title, term...) AND
    • event_leader(ID, leader_id, organization_id, role, title, term...)
  2. one table with entity_type = 'project', 'event', or 'org':
    • role(ID, leader_id, entity_type, entity_id, role, title, term...) OR
    • role(ID, leader_id, event_id, project_id, organization_id, role, title, term...)

Which choices would make it easier to find out who can edit a particular event (from its project, if any, its sponsoring organization) and find out which events, projects, and organizations a particular leader can edit?

I've attached erd's for the various choices.

Consolidated model - version A Consolidated model - version B Separate tables model

Any advice and strategies would be greatly appreciated!

1 Answer 1


I would use separate tables for the project-sponsor and event-sponsor relationships. You are making two many-to-many relationships here, and that just seems like the right way to do it to me.

Can each project/event/org have more than one leader? If not, why not simply put the leader id in the project/event/org table? Otherwise, I would use three tables for the same reason as above: you are describing three many-to-many relationships.

  • Yes, each project can have more than one leader. So can organizations, so can events. Everything is as complex as possible in this system. ;-(
    – dac
    Commented May 22, 2011 at 2:33
  • And each leader can be in more than one organization and in more than one role in an organization.
    – dac
    Commented May 22, 2011 at 2:46
  • Yep. Many-to-many relations, which require a table for each such relation to model.
    – andrewdski
    Commented May 22, 2011 at 2:50
  • So we're looking at the multiple tables model, which is where I started. :) I could use some help with the queries. When a leader logs in, I have to determine which organizations, events, and projects that leader can edit.
    – dac
    Commented May 22, 2011 at 17:55

Your Answer

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

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