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.

What's the relationship between users, divisions, and organizations? — Mike Sherrill 'Cat Recall', Apr 09 '11 at 01:46
Organization to user is 1 to many. Divisions are not yet implemented, but those will probably 1 to many with an Organization, and probably 1 to many with users, but maybe many to many. — Ryan, Apr 11 '11 at 15:22

score 2 · Accepted Answer · answered Apr 08 '11 at 23:03

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.

score 2 · Answer 2 · answered Apr 08 '11 at 23:04

If the CalendarEvents for each entity (User, Organization, and Division) are mutually exclusive, I might start out with three identical tables of events: UserCalendarEvents, OrganizationCalendarEvents, and DivisionCalendarEvents.

A better solution, though, may be to define those as three tables of links:

UserCalendarEvents
  user_id
  calendar_event_id

OrganizationCalendarEvents
  organization_id
  calendar_event_id

DivisionCalendarEvents
  division_id
  calendar_event_id

score 1 · Answer 3 · answered Apr 08 '11 at 22:53

1

Yes. There is a technique called "morphing" which is appropriate for your case. Your CalendarEvents table should have a field called "owner_type" and another field called "owner_id". "owner_type" would indicate the table to which "owner_id" is a foreign key for the particular row. If owner_type is 1, then owner_id is a user_id; if owner_type is 2, then owner_id is an organization_id. And so forth.

answered Apr 08 '11 at 22:53

dionyziz

2,394
1
23
29

2

What are the odds that every query ever written by any consumer will respect the owner_type column? You can't join here, so you're either writing three queries and branching in your code, or executing two queries every time. No thanks. – Alain Collins Apr 08 '11 at 22:57
I was also thinking of going this route, which would have been a cleaner version of my initial solution. It still left me with the issue of having to write more logic into the queries than I wanted. Thanks though. – Ryan Apr 11 '11 at 15:20

score 1 · Answer 4 · edited May 23 '17 at 12:26

One table column for many fk tables? .

and

multiple tables need one to many relationship .

If you want the DBMS to enforce the integrity rule that any calendar event is always either for an X, or a Y, or a Z (and just one of them), then you'll have to create three tables.

You can always create a view of "all calendar events" by UNIONing them together (after projecting away the owner column, of course). Obviously, that view is not updatable.

If you set up three separate tables with only a "link" to a "shared" events table, you still won't be guarded from having "orphaned" events.

Trying to avoid multiple parent tables

4 Answers4