Geek Answers Handbook

Delete a record in one table that stores properties for another table

I have a problem with the design of the database, which I have been doing for some time, but cannot get the correct 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 of the physical attributes of house . In the software user interface, users select a scheme, then click the Create button. The house is built and stored in the house . To describe how the house should be built, there are several other tables, for example house_schema .

In a simple design, the foreign key seems to work well. However, this causes a problem when the builder decides to remove the scheme, which, in their opinion, is outdated. There are already some houses built from the circuit, and the foreign key does not allow you to remove it. If we change the foreign key to DELETE ON CASCADE , then these houses will lose the information from which it was built.

What is the best design template to solve this problem? What I can imagine is to have a duplicate house_schema table, once the house is 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 several similar tables with house_schema . This seems to violate database normalization rules.

Does anyone have a good idea?

+4
source share
5 answers

If you want to keep a history of which diagram was used to create the houses, a typical solution would be to implement soft deletion in your house_schema table.

Instead of actually deleting the rows in house_schema you need

  • add Active column to table
  • set this column to false when the schema is out of date
  • configure the application so as not to show an inactive circuit

Please note that there are quite a lot of materials about soft removal, both with recommendations and with.

From personal experience, we use soft deletion for individual elements (drop-down lists) in our main application without any mentioned problems.

When an item becomes obsolete, it must be retained wherever it is used, but for new documents it should no longer appear in drop-down lists. I have yet to find a better solution to be able to handle this scenario other than soft deletion.

+3
source

There are several possibilities:

  • You may have a “Deleted” field in the house_schema file that you would indicate to indicate that the schema no longer exists. This would mean a change in many queries.

  • You may have a "non-removable" default scheme, which will be returned to when you delete it at home.

0
source

As a simple solution, you can add the "deleted" to house_schema flag. This way you keep historical records. When the available house_schema entries are shown to the user, the deletion filter is not removed.

0
source

It is possible to add "deprecated_schema", where you simply copy the line house_schema, which should be deleted, to the section of obsolete holdings. You will need a field to store the source field of the house_schema identifier so that you can get the correct entry in the home table.

Then you can remove house_schema from the table and still maintain access to houses based on this scheme.

0
source

To add another point of view, did you consider that perhaps the data stored in * house_schema * are actually attributes of the house and should be stored as such?
This can be seen as a case of excessive normalization.

0
source

More articles:


All Articles