Delete record in one table which stores properties for another table

Question

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, house_schema and house as follows:

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`
}

The house_schema table stores some physical attributes of house. On a software UI, users select a schema then click a "build" button. A house is built and is stored in house. There are some other tables like house_schema to describe how a house should be built.

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 DELETE ON CASCADE, then those houses lose the information it was built from.

What's the best design pattern to handle this problem? What I can imagine is having a duplicate table of house_schema, once a house has been built, copy the row in house_schema to the duplicate table.

But, this leads to a lot of duplicate tables in the database as I have multiple similar tables with house_schema. It seems to violate database normalization rules.

Does anybody have a good idea?

score 3 · Accepted Answer · edited May 23 '17 at 12:27

If you like to retain a history of what schema's were used to build houses, a typical solution would be to implement a soft delete on your house_schema table.

Instead of actually deleting the rows in house_schema you would have to

add a Active column to the table
set this column to false when a schema is obsolete
adjust your application to not show the inactive schema's

Note that there's quite some material about soft delete, both advising against and for.

From personal experience, we employ soft delete on selectable items (drop down lists) in our main application without any mentionable problems so far.

When an item becomes obsolete, it's value needs to be retained wherever it's used but for new documents, it shouldn't show up in de drop down lists anymore. I yet have to come across a better solution to be able to handle this scenario other than soft delete.

score 0 · Answer 2 · answered Jul 01 '12 at 20:47

There are some possibilities:

You could have a "Deleted" field in house_schema which you would flag in order to indicate the schema no longer exists. That would mean changing many queries.
You could have an "un-deletable" default schema to which, when one is deleted, its houses would fall back to.

score 0 · Answer 3 · answered Jul 01 '12 at 20:53

0

As a simple solution you could add a flag "deleted" to house_schema. That way you keep the historic entries. When showing available house_schema entries to the user, filter for not deleted.

answered Jul 01 '12 at 20:53

Rainer Schwarze

4,725
1
27
49

score 0 · Answer 4 · answered Jul 01 '12 at 21:29

Possibly an addition of a "deprecated_schema", where you simply copy the house_schema row to be deleted to the deprecated holding section. You would need a field to store the original house_schema ID field, so that you can still get to the correct entry in the house table.

Then you can delete from the house_schema table, and still retain access to the houses based on that schema.

score 0 · Answer 5 · answered Jul 02 '12 at 01:52

0

Just to add another point of view, have you considered that perhaps the details stored in *house_schema* are in fact attributes of the house and should be stored as such?
This could be seen as a case of over-normalization.

answered Jul 02 '12 at 01:52

TelJanini

835
9
25

I store the schema in separate table because it's some sort of configuration before building house. User selects a configuration then a house is built from it, now the configuration becomes attributes of house. But before that, it's not attribute anyway as there is house. – zx_wing Jul 04 '12 at 00:27

Delete record in one table which stores properties for another table

5 Answers5