How to model a 1..1 Composition Relationship

database-designforeign keyreferential-integrity

We have a couple of entities/relationships of the following kind:

  • Entity Event has a TimePoint
  • The TimePoint contains time information about the event. It belongs to that event and cannot be used by any other entity. Without the corresponding Event, the TimePoint has no meaning, it is orphaned. In short, it looks like a composition to me.
  • The Event, in turn, must have a TimePoint. There is no event without a time. It depends on the TimePoint.

The question I am facing is, which entity has the foreign key to the other entity?

Option 1:

FOREIGN KEY (Event.TimePointID) references TimePoint.ID

where as Event.TimePointID NOT NULL

Problem: When the Event is deleted, the TimePoint is left orphaned.

Option 2:

FOREIGN KEY (TimePoint.EventID) references Event.ID

where as TimePoint.EventID NOT NULL

Problem: When the TimePoint is deleted, the Event becomes invalid and makes the application crash.

I think this is a pretty common issue and there must be a good old DBA's advice to how to model such a relationship…?

Update: The example above is simplified to only represent the relationship in question. One reason why we need two separate entities is because a TimePoint can be owned by a record from a different table instead of an Event too. But it must always be owned by exactly one record from exactly one of either table.

Best Answer

Try thinking from the other end of the SDLC. What queries will you be writing? Would there be a performance advantage to holding the foreign key in one place or the other so only one table had to be read from disk?

From the entity types' names I would infer that the foreign keys would never be updated. If so then there is little harm in storing a FK in both Event and in TimePoint. It would make your INSERT logic ever so slightly more complex. If it saved a read-time it might pay off. You know your system best.

Related Question