How to model a 1..1 Composition Relationship

Question

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.

Answer 1

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.

Answer 2

Since a TimePoint can be owned by either an Event or some other table, it makes sense for the foreign key to be on the Event side (and on whatever the other table is).

You're going to have to build some procedural logic to enforce the integrity of this relationship, since there is no declarative way to say: "Each record in table A can have only one record in table B, and that record must be the same as in table A." "Each record in table A must point to a record in table B, which must point back to that same record in table A." (rephrased per request)

On the Other Hand...

I'm having trouble understanding the 1:1 nature of this given that you say that some TimePoint records can relate to others, but each one much have an Event or something else. Does this not imply that an Event effectively has more than one TimePoint? If that is true then keeping the foreign key on the Event side wouldn't work.