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'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!

share|improve this question

migrated from stackoverflow.com May 22 '11 at 10:05

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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 May 22 '11 at 2:33
    
And each leader can be in more than one organization and in more than one role in an organization. –  dac May 22 '11 at 2:46
    
Yep. Many-to-many relations, which require a table for each such relation to model. –  andrewdski May 22 '11 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 May 22 '11 at 17:55

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.