title: Delete record in one table which stores properties for another table
body: <p>I have a database design problem which I have been researching for a while but cannot get a proper answer. Let's say we have two tables, <code>house_schema</code> and <code>house</code> as follows:</p>
  
  <pre><code>house_schema {
        id big int,
        house_height int,
        house_width int,
        house_decoration vchar(1024),
        build_date  timestamp,
        primary key id,
  }
  
  house {
        id big int,
        owner vchar(255),
        price big int,
        house_schema_id big int,
        primary key id,
        foreign key fk_house_house_schema_id (`house_schema_id`) reference `house_schema`.`id`
  }
  </code></pre>
  
  <p>The <code>house_schema</code> table stores some physical attributes of <code>house</code>. On a software UI, users select a schema then click a "build" button. A house is built and is stored in <code>house</code>. There are some other tables like <code>house_schema</code> to describe how a house should be built. </p>
  
  <p>In a simple design, a foreign key seemingly works well. However, it gives rise to a problem when the builder decides to remove a schema that they think is out of date. There are already some houses built from the schema and the foreign key prevents it from deleting. If we change the foreign key to <code>DELETE ON CASCADE</code>, then those houses lose the information it was built from.</p>
  
  <p>What's the best design pattern to handle this problem? What I can imagine is having a duplicate table of <code>house_schema</code>, once a house has been built, copy the row in <code>house_schema</code> to the duplicate table. </p>
  
  <p>But, this leads to a lot of duplicate tables in the database as I have multiple similar tables with <code>house_schema</code>. It seems to violate database normalization rules.</p>
  
  <p>Does anybody have a good idea?</p>