We have a couple of entities/relationships of the following kind:
- Entity
Event
has aTimePoint
- The
TimePoint
contains time information about the event. It belongs to that event and cannot be used by any other entity. Without the correspondingEvent
, theTimePoint
has no meaning, it is orphaned. In short, it looks like a composition to me. - The
Event
, in turn, must have aTimePoint
. There is no event without a time. It depends on theTimePoint
.
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.
TimePoint
as a separate entity.TimePoint
can be owned by a record from a different table instead.1:1
or a1:n
relationship between Events and Timepoints. Or two relationships.