0

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?

bhootjb
  • 1,501
  • 1
  • 21
  • 33

2 Answers2

3

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.

fredt
  • 24,044
  • 3
  • 40
  • 61
1

enter image description here

OR, depending how you define "Package".

enter image description here

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • Nice picture, but neither design reflects that `TourID` in `Traveller` is redundant/denormalized. – Hazzit Mar 09 '13 at 22:03
  • @Hazzit -- explain, please? – Damir Sudarevic Mar 10 '13 at 15:55
  • PackageID and TourID have a N:1 relationship. A tour has *n* packages, a package belongs to 1 tour. Although a traveller "first picks a tour, then selects a package", storing the package id is enough, as it implies which tour was chosen. Both of your proposed `Traveller` tables store a `TourID` *and* a `PackageID`. That is redundant. It also makes it harder to maintain the integrity of the system - one could store a `PackageId` in the `Traveller` table that belongs to a totally different `TourID`. – Hazzit Mar 10 '13 at 16:03
  • @Hazzit -- defined this way, it is the **first option** above, it uses composite key for `Package` -- as dependent (weak) entities should; because (as in this option) a `Package` can exists only in a context of an independent `Tour`. The second option shows `Package` as an independent entity-- different definition. There is absolutely nothing "denormalized" here. – Damir Sudarevic Mar 10 '13 at 16:24
  • Regarding your first option, I must admit that I misread `TourPackageNo` as `TourPackageId`, which would not have been normalized. I now understand that you are suggesting a compund primary key for the `Packages` table, which I still don't like, but is a correct answer. As such I'll remove my downvote. (Option 2 models an entirely different scenario which does not pertain to the original question) ... Meh, just found out that I cannot undo my downvote unless the answer is edited... sorry. – Hazzit Mar 10 '13 at 16:52