MySQL data modeling help

database-design

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!

Best Answer

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.