I have a simple DB schema describing certain objects that belong to a historical time period of a country and (optionally) a subdivision of that country at that period. The contrived UML class diagram is given below:
The relations are as follows:
countrycan have one or more historic time
periods(defined by a year interval).
objectis an entity that is assigned to a historical
periodand may optionally be assigned to a
regionis an optional subdivision of a period (if a country is divided into smaller pieces, i.e. Germany into West/East Germany in 1949-1989).
This schema is flawed by having a data redundancy problem: when an object is assigned to a region, it has two foreign keys pointing to
period. The pointed
region row also has a foreign key that has to be pointing to the same
period. Thus, if region is moved to another period, keys have to be replaced in multiple places.
I've thought of two undesirable solutions:
- by removing the region—period relationship but then region loses its logical connection to a period (by object—region relation, a region may belong to many periods, which is not desirable);
- removing object—period relationship but because object's association to region is optional, object would lose it's connection to country.
Do you have any architectural ideas that would solve the data redundancy problem? I'm using Django ORM for defining my models.