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:
- 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.
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.