When I normalize a database, it always feels like I cannot predict Cascading, leaving broken relationships and trash queries.

  • 4
    Its very difficult to refactor an active database with interconnected data.

    Fist rule is to really understand the data and queries.

    Once you know and understand you need to plan out the refactorisation carefully.

    And take it in small steps verifying that all works in between.

    Often I start by adding the new tables and columns and updating so everything is written there.

    Then verify that the new queries or solutions gets the same result and once everything is running on the new format remove the code writing the old.
    This takes time, for a large complex database possibly weeks or months if you want to ensure uninterrupted service.

    If you need to do the switch faster you usually need to spend more time planning instead.
  • 1
    Predict Cascading...

    Don't do that.

    Testing in databases is important.

    Watch and observe is better than "think and calculate possible outcomes".

    I tend to joke that every database has it's own personality, which is true in some aspect - different configurations, queries, runtime load etc leading to different behaviour.

    Trying to calculate the outcome is nearly impossible imho.

    Setup a second database, test and monitor there. Less dangerous, too
Add Comment