How to model a 1..1 Composition Relationship 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.
Personally I would put the TimePoint attributs into the event table. But if you prefer two tables, you should be able to achieve that with foreign keys together with NOT NULL columns. 

But that will only work if you have a database that supports deferrable constraints (Oracle and PostgreSQL come to mind), otherwise there is no way of inserting new values (unless you want to have some "dummy" TimePoint row - which is really a hack - I'd definitely prefer a single table solution over a "magic row").

I don't think you need separate columns for the FK columns, as both tables are so tightly coupled.

Something like:

create table event
(
   event_id integer not null primary key,
   ... other columns ...
);

create table timepoint
(
   event_id integer not null primary key,
   ... other columns ...
);

alter table event 
    add foreign key (event_id)
    references timepoint (event_id)
    deferrable initially deferred;

alter table timepoint
    add foreign key (event_id)
    references event (event_id)
    on delete cascade
    deferrable initially deferred;

insert into event (event_id) values (1);
insert into timepoint (event_id)  values (1);
commit;

insert into event (event_id) values (2);
insert into timepoint (event_id)  values (2);
commit;

-- This will delete the timepoint as well!
delete from event
where event_id = 1;


The inserts work because the FK constraints are deferred. The ON DELETE cascade for the FK between timepoint and event ensures that the timepoint is deleted when the event is deleted. Deleting a timepoint is not possible because that constraint is not defined as cascading. If you want to delete the associated event when the timepoint is deleted, define that constraint as cascading as well. 

(The above example works in PostgreSQL and should work in Oracle too)

But again: I'd put everything into the event table.