1

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:

enter image description here

The relations are as follows:

  • a country can have one or more historic time periods (defined by a year interval).
  • object is an entity that is assigned to a historical period and may optionally be assigned to a region.
  • region is 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 region and 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.

  • "region is an optional subdivision of a period" - is that a typo? Did you mean subdivision of a country? – Michael Green Nov 9 '16 at 10:11
  • Not really. Region is a subdivision of a country in a given timeframe. For example West/East Germany is only a subdivision of the country at a given time period (Country: Germany -> Period: Divided Germany 1949-1989 -> Regions: [West, East]) – Crossfire Nov 9 '16 at 10:35
1

Looks like you can remove the Period FK entirely from the object. When assigned to a Region, it is already assigned to the Period assigned to the Region. Define a special Region -- a NULL Region, "not a Region", Pass-Thru Region, an all inclusive region, whatever -- to act as a placeholder for a Period when you mean "assigned only to Period rather than a Region."

This, I think, would involve the minimum amount of changes to your schema: drop the Period FK field from the Object table.

  • 1
    This does complicate things a bit but thanks for your thoughtful advice! – Crossfire Nov 10 '16 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.