1

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.

5
  • 3
    If it's a true 1:1 relation then there is no need to have TimePoint as a separate entity.
    – user1822
    Nov 15, 2012 at 16:42
  • @chcicodoro: Do you mean that an Event can be actually related to multiple Timepoints, while the "basic" is the first one of them? Nov 16, 2012 at 13:10
  • @ypercube: I have removed the confusing part. It is not relevant to the question. The updated "update" section explains the main point for having two entities: TimePoint can be owned by a record from a different table instead.
    – chiccodoro
    Nov 16, 2012 at 13:57
  • I'm still not convinced on what your requirements are. If it is a 1:1 or a 1:n relationship between Events and Timepoints. Or two relationships. Nov 16, 2012 at 14:16
  • It is a 1:1 relationship, or more precisely a 0..1 : 1 relationship: Every Event owns exactly one time point, and each record from the other table owns exactly one time point. So a time point is always owned either by one event or by a record from the other table.
    – chiccodoro
    Nov 20, 2012 at 8:33

3 Answers 3

1

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.

1
  • Thanks. You revived a rather old-ish thread but reviewing it this looks like the most accurate way to tackle this problem.
    – chiccodoro
    May 2, 2014 at 6:50
3

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.

5
  • Thank you. I have elaborated a bit on why we have two entities in this case. I think in the essence you're suggesting to have 2 foreign keys but make them deferred. I was thinking about this, too. Is this a common practice or do some DBAs consider it a bad design?
    – chiccodoro
    Nov 16, 2012 at 8:12
  • @chiccodoro: why would a deferred constraints considered to be "bad"?
    – user1822
    Nov 16, 2012 at 8:20
  • I consider them not pretty. Not inherently bad. What is bad (in my sole opinion) are the cycles in the references diagram. And in this particular example, you already commented that you would have the Event and Timepoint as a single point. I agree. Another reason is that with 2 tables, as above, the second foreign key constraint is redundant, it has no more information that the first. Nov 16, 2012 at 10:29
  • It is there, only to ensure that no row is added in the Event table without a corresponding row in the Timepoint table. I would keep only the Timepoint->Event foreign key and allow the Inserts into the Event table to be done through a procedure that either succeeds to insert into both tables or fails. Nov 16, 2012 at 10:33
  • 1
    @ypercube: I do consider them pretty to be honest. The first database I used (rdb/VMS) actually allowed this to be set on transaction level. That was pretty cool. And I'm a strong advocate of putting as many "declarative" restrictions into the model as possible. Even if you think you can control every access, there might still be rogue SQL scripts that "forget" to use the procedure.
    – user1822
    Nov 16, 2012 at 10:40
1

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.

8
  • "Each record in table A can have only one record in table B, and that record must be the same as in table A." - can you rephrase that? Am not sure how a record from table A can be the same as in table B
    – chiccodoro
    Nov 16, 2012 at 13:00
  • As for the On-the-Other-Hand part: The Event only points to the first occurrence of the TimePoint. The time point can be either a single occurrence or reflect a recurrence. Maybe it would be interesting to discuss the general model of this, but I'd rather keep this out of this thread - it is really just one instance for an issue that we have in multiple places.
    – chiccodoro
    Nov 16, 2012 at 13:08
  • What do you mean with "there is no declarative way"? @a_horse_with_no_name has provided a way, using deferrable constraints. Nov 16, 2012 at 13:09
  • @ypercube - Deferrable constraints can declaratively require that A points to B and B points to A, but they can't require that A and B point to the same records in each direction. Record A.1 could point to record B.2 and record B.2 could point to record A.3. This would satisfy a deferred mutual foreign key constraint but it doesn't satisfy the business requirement. Deferring constraints let's you temporarily break the rules, they don't let you define new kinds of rules (e.g. cross-table check constraints).
    – Joel Brown
    Nov 16, 2012 at 13:17
  • I don't see how this would happen (A.1 point to B.2 and B.2 point to A.3) when the two referencing/referenced columns are the same and primary keys. Nov 16, 2012 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.