Search code examples
hibernatedatabase-designhsqldbreferential-integritydata-integrity

Enforce integrity constraint for closed loop foreign key constraints

The problem arises from the following three tables -

Tour
 - Id

TourPackage
 - Id
 - TourId

Traveller
 - Id
 - TourId
 - PackageId

The foreign key constraints are in place. However, I also wish to enforce that Traveller's Package must also refer to the same Tour that the Traveller is referring.

Traveller.Tour = Traveller.Package.Tour

I might be able to easily enforce this on business layer, but could I do this in the database? Or should I rethink my design? Note that I don't plan to extract the foreign keys in a new, relationship table. I think that Tour and Package are part of a Traveller entity, and hence should reside in the entity itself.

UPDATE

My application runs within the context of a single Tour. Thus, all the data that I load will point to a single tour. Also, most of the data comes from Traveller table. So, it makes sense to have a direct TourId to the table. Anyways, I look at the relationships in this way.

  • A tour has several packages.
  • A traveller enlists himself for a tour.
  • The traveller selects package for the subscribed tour.

So, I guess a traveller referring to tour as well as package is sensible. Am I wrong here?

Solution

  • First of all, it's possible to use a foreign key to enforce the TourId and PackageId references in the Traveller table to be consistent. FORIEGN KEY TRAVLELLER(TOURID, PACKAGEID) REFERENCES PACKAGE(TROURID, ID) together with a UNIQUE(TOURID, ID) on the TourPackage table.

    Regarding your data model, if the tour has several packages and the traveller enlists for a single package, then there is no need for a TourId field in the Traveller table. You can always find the TourId by joining the three tables.

    But if the traveller is allowed to enlist for more than one package in a tour, then you need a relationship table to link the traveller with multiple packages.