Help defining a chained one-to-many relationship tables

by djfried   Last Updated August 13, 2019 22:06 PM

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.



Related Questions


Updated May 13, 2015 20:02 PM

Updated May 13, 2015 20:02 PM

Updated March 10, 2016 07:02 AM

Updated June 08, 2019 17:06 PM

Updated May 13, 2015 15:02 PM