Trying to avoid multiple parent tables

Question

A new requirement has come into an existing application. Current, we have an organization table, and it has a child table CalendarEvents. Now, the request is to allow either the User table, the Organization table, or the Division table own calendar events. I am thinking something needs to change because right now, this would leave me with creating the following table structure:

• Organization (organization_id)
• User (user_id, organization_id)
• Division (division_id),
• Calendar (calendar_id, organization_id, user_id, division_id),
• CalendarEvents (calendar_event_id, calendar_id)

I am trying to avoid linking Calendar to multiple parents. Is there are better way to do this that I am missing? (An organization/user/division can have multiple calendars, but only one org/user/division can own a calendar)

Thanks for any input.

Answer 1

Since User instances and Organization instances can have their own events, I'd be inclined to make separate tables:

Organization
OrganizationCalendarEvents (with FK to Organization)
User
UserCalendarEvents (with FK to User)

In this way, the two entities can control their own events. In addition, if you keep the structure the same, you could use a single base class in your middle-tier which can load from either table.