How to maintain two versions of same entity available for edit simultaneously with relationship?

Question

The problem

Consider these database tables:

1. Product
2. Order
3. Order Details
4. User

Product has columns:

Product_Name, Product_Description, Product_Size, Product_Cost, Product_Unit 

Order has columns:

Order_number, Order_Total, Order_Status, Order_Payment_Status, Order_UserId (Fk of user table), Order_date

Order Details has columns:

OrderDetails_OrderId(Fk of Order table), OrderDetails_ProductId (Fk of Product table), OrderDetails_Quantity

User has columns:

User_Name, User_Phone (unique), User_Email (unique), User_Address

Consider, order statuses to be placed, packed, delivered, canceled, closed.

Now, There are three orders for the user u1:

1. order O1 -> Placed status (Editable by user)
2. order O2 -> Placed status (Editable by user)
3. order O3 -> Closed status (Non-editable by the user, but editable from admin)

Now the scenario is that the user u1 updates his information. This updated information should start reflecting in O1 and O2 only because they are still in the placed status; while O3 was already closed for the user and is now open only for admin's edit - so O3 should still reflect the old user information that was there previously. With the current database structure - this is not possible.

Similarly, if the admin edits product that was there in closed order, then the edits should not be displayed in the closed order.

As you may have figured out, the current structure depicted above is a simple foreign key related structure, wherein edit in one will obviously reflect directly to all related entities.

What solutions I figured out?

Solution 1: Versioning Never update any row/entry. Always add a new row for any change (Soft updates). Keep adding rows with some tag/id/timestamp/audit trail (who edited) and map the version with the order table using mapping table.

i.e.

User_Name | User_Phone | User_Email     | User_Address | Version/Timestamp
abc       | 123        | abc@email.com  |someaddres    | v1
abc       | 234        | abc@email.com  |someaddress   | v2

new mapping table
version | order_id 
v1      | o3

Drawbacks of this solution are:

1. Same table multiple entries, for the same entity - then we won't be able to use unique keys. Here, phone and email are unique, but if we pick this approach then unique indices have to be removed.
2. All those tables (unrelated to order) that would have the foreign keys of the User table will have an impact. For example, the user_feedback table has the user's foreign key only, but now since there are multiple entries with a different version of the same user, this table will be impacted needlessly.
3. When the number of users would increase, performance will be impacted in select queries.
4. User's email is the identity, which is used for logging in. Its duplication is anyhow not possible in the same table.

No this is not audit trailing! As per our requirement, the old information that we want to preserve for o3, should still be kept editable. Therefore, those edits shall also have to be audited. So audit trailing will be a separate wrapper altogether.

Solution 2: When order closes, create a new table with columns saving json/dump of all respective tables i.e.

new table
order_id | JsonOfUser         | JsonOfProductDetails | ...
o3       | {"name":"abc",...} | ...                  |

Drawbacks of this solution:

1. The things dumped are to be editable, but here the dumped data is difficult to edit because now the table has changed, and this table has a string/jsonb column that is effectively going to get edited, and other navigations are removed (denormalized) so all calculation changes that potentially would happen due to edits, would also have to be done manually.
2. Audit trailing of the edits in this table will be cumbersome because we'll be auditing the json edits here.
3. Deep level child jsons - increases code complexity.

Solution 3: Create copies of all tables with structure intact, that are related to order according to status events i.e.

User_Common

User_Closed

For order O3, upon closure, all details of user_common will be copied to User_closed, and order O3 that had the foreign key of User_common table will be changed with the foreign key of User_Closed table. Now any changes in the o3 will effectively be over old data and all other open/placed orders can still get the updated information from User_common.

Drawbacks of this solution:

1. Suppose there happen to be 10 such tables related to order with this requirement, then each table's copies will have to be made
2. Each entity is now effectively represented by two tables based on event/status of the order - syncing issues and data-keeping issues may happen - i.e. maintainability.
3. Foreign keys are changing here for the order table. So effectively in the order table, there would be two foreign key columns: one for user_common, and other for user_closed. So when the order is open, the user_closed foreign key will remain null, and when order closes, it will get filled. Before that, 1 data operation will still occur, one to copy the information on order closure from the user_common table to user_closed.
4. In code, we'll always have to make a DB check for whether the lookup should be made in a common table or closed table based on order status (another DB call) - leading to code-level cognitive complexity

This was a minimal dummy replication of our requirement and proposed solutions that are thought of in research. What is the practical possible design that can adhere to this requirement without adding needless complexities?

Answer 1

Use the approach that has the User table add another column for a valid_since column. Apply the "never delete" strategy to Users.

If you measure performance issues add a persited/materialized (in memory) view for User that only shows the most current address - use that to get the user_id for newly placed orders and joins to show orders that are open. Joins for existing orders that use foreign keys into User mostly don't care about how many actual user_id's there are (simplified).

Use an after_insert trigger on User to propagate the new user_id to all entries of the Order to table that should reflect those changes, ignore closed orders for that update. This will be, in respect to the orders, a rather small update - how many open orders is one user allowed to have? 10? 20? 50?

Cleanup user data regularly in case they change but never order anything - those User entries can get erased.

You ensure integrity on database level that way - if you want add a report for users that change their details more then thrice a day (or limit those changes frontend wise).

---

Most of your user fields should also be 1:N relations - I have at least 3 telephone numbers and might use 2 addresses (living + shipping) and more then 1 email is a given.

Changing out those to own tables with a "active" might elivate the need to create full User copys. Based on buisiness needs the "address" used to ship stuff to might be worth remembering, not so much for the mobilenumber used to order or the email used to send confirmation towards but that is a business decision.

Unless you have a very active shop with millions of different users that order hundreds of orders each and change their details twenty times a month you wont get into problems using any of this with currently state of the art databse system. It seems to me this is rather a thought experiment then based in actual needs?