Help defining a chained one-to-many relationship tables

database-designoracle

Sorry if the title is confusing but I couldn't think of a way to properly word this question without laying out what I am trying to do.

Basically I have 3 tables and I'm trying to figure out how to best relate them all given the constraint that each Template should have a region but necessarily doesn't have to country.

 Template
  id
  name
  description
  country_id

 Country
  id
  name
  region_id

 Region
  id
  name

Two solutions that I've come up with but am not really satisfied with are:

  1. add region_id column to template table. The problem I have with this solution is creating the circular relationship between the region_id in country and in template. Enforcing that region_id is the same in country and template tables also seems difficult to enforce.
  2. add region specific country records to the country table that have no real country value but have a relationship to an existing region. For example:

    Country:
    id:5
    name: North America
    region: 1 -> North America

Solution 2 seems the simplest but I also feel like its not the most elegant solution because I am creating these "fake" country records.

Best Answer

Since Template must be related to a Region, you need a foreign key from Template.region_id to Region.id

Template.region_id will be NOT NULLable.

Template.country_id will be NULLable.

Make a unique constraint on the composite key (Country.id, Country.region_id)

Then you can create a foreign key from (Template.country_id , Template.region_id) to (Country.id, Country.region_id)

This ensures that if the Template is related to a Country, then the Template.region_id is the same as the related Country.region_id

Luckily (for you) when a row's composite foreign key contains one or more null values, then the row is "valid" regardless of the existence of rows in the referenced table. So having a NULL Template.country_id and a NOT NULL Template.region_id will be fine.

One interesting side effect of this "doubly constrained" system is that it is difficult to update the region_id column of a Country. Any attempt to update Country.region_id will cause a constraint violation if there are any Templates that contain references to that Country, since the (Template.country_id , Template.region_id) pair will not refer to an existing (Country.id, Country.region_id).

If you must allow this type of update, then you can use an ON UPDATE CASCADE clause when creating the (Template.country_id , Template.region_id) foreign key.