Avoiding Foreign Key Repetition in Related Tables – PostgreSQL Database Design

application-designdatabase-designforeign keyormpostgresql

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.

Best Answer

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.

Related Question