0

Suppose we are designing a database with the entities Apartment and Neighborhood. For the apartment we want to store its code identifier, address, and size (area) and for the neighborhood its code identifier, name, size (area), and number of buildings. An apartment may only exist on one neighborhood and a neighborhood contains many apartments. enter image description here

As the neighborhood grows over the time, the mayor decides to split it out. The rule doesn't change (an apartment may only exist on one neighborhood and a neighborhood contains many apartments), so we create the new neighborhood row(s), update the old neighborhood row(s) and then update the FK(s) for the affected apartment(s). However, we want the DB to answer "On what neighborhood(s) was some apartment associated to before?". Since we updated the FK, we don't have any records of what neighborhoods an apartment was associated to before.

How is the proper way of modeling this so the DB can answer that question by a query? (This same scenario happens on some other entities from the original DER).

2
  • The topic to smoothly research is "slowly changing dimensions".
    – mustaccio
    Nov 26, 2022 at 13:15
  • Yep, that was it. Would you like to submit an answer so I can choose it as the solution? Nov 26, 2022 at 16:15

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.