3 Made some rewordings; modified formatting.
source | link

Marking a row as removed or deleted - is: Is it a good design practice?

This is a theoreticaltheoretical* design question about marking rows as removed/deletedremoved or deleted. I assume rows should be marked as removed whenever there is a need to remove row'sthe pertinent data, but the row should stay in DBthe relevant database table to maintain referential integrity.  

* Theoretical, within the scope of this question, means that it should not be related to concrete example and also not related to concrete relational database.

For the sake of the question I have chosen a classical example: shop'sA Shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.

We have Ordersan Orders table, which references Usersa Users table. If we try to deleteDELETE a user -user row, we will lose all referenced orders orders (assuming we have strong referencing). Therefore instead of deletiona DELETion of usera user row we may mark the usersuch row as removed'removed' (i.e., have a bit IsRemoved=1BIT (or boolean) column SET to '1', e.g., IsRemoved = 1) and clear his login/password/first name/last name fieldslogin, password, first name, last name columns to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other useful meaning it has.

But there is an alternative:, instead of having an OrdersOrders->Users>Users relationship, we can have a different designstructure: OrdersOrders->Customers>Customers->Users. Customers>Users, where the Customers table has no personal data. When the usera user wants to quit -, we simply delete his UserDELETE the pertinent User row, but Customer andCustomer along with its related OrdersOrders stay stored in DBthe database. No need to mark row as removedremoved, no need infor additional business rules for "removed"to handle the removed rows behavior. I believe this approach is better and may be used for any situation where the "IsRemoved"IsRemoved row approach is used.

Question

Question: do Do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?

P.S. ofOf course, it is possible to referential integrity with NULLs in foreign keysFOREIGN KEYs, however this approach is out of scope. While it may be useful in shop'sthe Shop's example, the question is theoreticalhypothetical, and shopshop is used only as example, not as a real problem (which maymay be solved with NULLs).

Marking a row as removed or deleted - is it a good design?

This is a theoretical design question about marking rows as removed/deleted. I assume rows should be marked as removed whenever there is a need to remove row's data, but the row should stay in DB to maintain referential integrity.  

For the sake of the question I have chosen a classical example: shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.

We have Orders table, which references Users table. If we try to delete a user - we will lose all referenced orders (assuming we have strong referencing). Therefore instead of deletion of user we may mark the user row as removed (i.e. have a bit IsRemoved=1) and clear his login/password/first name/last name fields to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other useful meaning it has.

But there is an alternative: instead of having an Orders->Users relationship, we can have a different design: Orders->Customers->Users. Customers table has no personal data. When the user wants to quit - we simply delete his User row, but Customer and related Orders stay in DB. No need to mark row as removed, no need in additional business rules for "removed" rows behavior. I believe this approach is better and may be used for any situation where the "IsRemoved" row approach is used.

Question: do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?

P.S. of course it is possible to referential integrity with NULLs in foreign keys, however this approach is out of scope. While it may be useful in shop's example, the question is theoretical, and shop is used only as example, not as a real problem (which may be solved with NULLs).

Marking a row as removed or deleted: Is it a good design practice?

This is a theoretical* design question about marking rows as removed or deleted. I assume rows should be marked as removed whenever there is a need to remove the pertinent data, but the row should stay in the relevant database table to maintain referential integrity.

* Theoretical, within the scope of this question, means that it should not be related to concrete example and also not related to concrete relational database.

For the sake of the question I have chosen a classical example: A Shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.

We have an Orders table which references a Users table. If we try to DELETE a user row, we will lose all referenced orders (assuming we have strong referencing). Therefore instead of a DELETion of a user row we may mark such row as 'removed' (i.e., have a BIT (or boolean) column SET to '1', e.g., IsRemoved = 1) and clear his login, password, first name, last name columns to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other useful meaning it has.

But there is an alternative, instead of having an Orders->Users relationship, we can have a different structure: Orders->Customers->Users, where the Customers table has no personal data. When a user wants to quit, we simply DELETE the pertinent User row, but Customer along with its related Orders stay stored in the database. No need to mark row as removed, no need for additional business rules to handle the removed rows behavior. I believe this approach is better and may be used for any situation where the IsRemoved row approach is used.

Question

Do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?

P.S. Of course, it is possible to referential integrity with NULLs in FOREIGN KEYs, however this approach is out of scope. While it may be useful in the Shop's example, the question is hypothetical, and shop is used only as example, not as a real problem (which may be solved with NULLs).

2 fixed spelling errors
source | link

This is a theoretical design question about marking rows as removed/deleted. I assume rows should be marked as removed whenever there is a need to remove row's data, but the row should stay in DB to maintain referential integrity.

For the sake of the question I have choosenchosen a classical example: shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.

We have Orders table, which references Users table. If we try to delete a user - we will looselose all referenced orders (assuming we have strong referencing). Therefore instead of deletion of user we may mark the user row as removed (i.e. have a bit IsRemoved=1) and clear his login/password/first name/last name fields to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other usefulluseful meaning it has.

But there is an alternative: instead of having an Orders->Users relationship, we can have a different design: Orders->Customers->Users. Customers table has no personal data. When the user wants to quit - we simply delete his User row, but Customer and related Orders stay in DB. No need to mark row as removed, no need in additional business rules for "removed" rows behavior. I believe this approach is better and may be used for any situation where the "IsRemoved" row approach is used.

Question: do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?

P.S. of course it is possible to referential integrity with NULLs in foreign keys, however this approach is out of scope. While it may be usefulluseful in Shop'sshop's example, the question is theoretical, and Shopshop is used only as example, not as a real problem (which may be solved with NULLs).

This is a theoretical design question about marking rows as removed/deleted. I assume rows should be marked as removed whenever there is a need to remove row's data, but the row should stay in DB to maintain referential integrity.

For the sake of the question I have choosen a classical example: shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.

We have Orders table, which references Users table. If we try to delete a user - we will loose all referenced orders (assuming we have strong referencing). Therefore instead of deletion of user we may mark the user row as removed (i.e. have a bit IsRemoved=1) and clear his login/password/first name/last name fields to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other usefull meaning it has.

But there is an alternative: instead of having an Orders->Users relationship, we can have a different design: Orders->Customers->Users. Customers table has no personal data. When the user wants to quit - we simply delete his User row, but Customer and related Orders stay in DB. No need to mark row as removed, no need in additional business rules for "removed" rows behavior. I believe this approach is better and may be used for any situation where the "IsRemoved" row approach is used.

Question: do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?

P.S. of course it is possible to referential integrity with NULLs in foreign keys, however this approach is out of scope. While it may be usefull in Shop's example, the question is theoretical, and Shop is used only as example, not as a real problem (which may be solved with NULLs).

This is a theoretical design question about marking rows as removed/deleted. I assume rows should be marked as removed whenever there is a need to remove row's data, but the row should stay in DB to maintain referential integrity.

For the sake of the question I have chosen a classical example: shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.

We have Orders table, which references Users table. If we try to delete a user - we will lose all referenced orders (assuming we have strong referencing). Therefore instead of deletion of user we may mark the user row as removed (i.e. have a bit IsRemoved=1) and clear his login/password/first name/last name fields to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other useful meaning it has.

But there is an alternative: instead of having an Orders->Users relationship, we can have a different design: Orders->Customers->Users. Customers table has no personal data. When the user wants to quit - we simply delete his User row, but Customer and related Orders stay in DB. No need to mark row as removed, no need in additional business rules for "removed" rows behavior. I believe this approach is better and may be used for any situation where the "IsRemoved" row approach is used.

Question: do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?

P.S. of course it is possible to referential integrity with NULLs in foreign keys, however this approach is out of scope. While it may be useful in shop's example, the question is theoretical, and shop is used only as example, not as a real problem (which may be solved with NULLs).

1
source | link

Marking a row as removed or deleted - is it a good design?

This is a theoretical design question about marking rows as removed/deleted. I assume rows should be marked as removed whenever there is a need to remove row's data, but the row should stay in DB to maintain referential integrity.

For the sake of the question I have choosen a classical example: shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.

We have Orders table, which references Users table. If we try to delete a user - we will loose all referenced orders (assuming we have strong referencing). Therefore instead of deletion of user we may mark the user row as removed (i.e. have a bit IsRemoved=1) and clear his login/password/first name/last name fields to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other usefull meaning it has.

But there is an alternative: instead of having an Orders->Users relationship, we can have a different design: Orders->Customers->Users. Customers table has no personal data. When the user wants to quit - we simply delete his User row, but Customer and related Orders stay in DB. No need to mark row as removed, no need in additional business rules for "removed" rows behavior. I believe this approach is better and may be used for any situation where the "IsRemoved" row approach is used.

Question: do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?

P.S. of course it is possible to referential integrity with NULLs in foreign keys, however this approach is out of scope. While it may be usefull in Shop's example, the question is theoretical, and Shop is used only as example, not as a real problem (which may be solved with NULLs).