Databases
StackOverflow2013
Postsdbo
PODelete record in one table which stores properties for another table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODelete record in one table which stores properties for another table
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBen
    1. USzx_wing
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODelete record in one table which stores properties for another table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODelete record in one table which stores properties for another table
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODelete record in one table which stores properties for another table
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload