Sample Header Ad - 728x90

How to model edited linked records of a multi-schema entity while preserving the original linked records?

0 votes
1 answer
44 views
If you have a base class parent. parent can have multiple child records, a one-to-many relationship.
parent schema:
+-----------+
| parent_id |
+-----------+

child schema:
+-----------+----------+
| parent_id | child_id |
+-----------+----------+
Then parent is used in another entity, table x, that can have many parents, again in a one-to-many relationship:
x_parent schema:
+------+-----------+
| x_id | parent_id |
+------+-----------+
Users can edit entities from table x to add and remove parents. While doing this, they can also edit the child records of the parent, however these changes need to be saved separately (as edits of x), not modifying the original parent or its children. How should this be modeled to keep the data normalized, and also to know which records to use - the original child records, or some edited ones? This is the trickiest part to me, as the absence of edited records could mean the user removed all the original child records, or has simply not edited the child records of parent. EG table x_child could look like this:
+------+-----------+--------------------+
| x_id | parent_id | child_edited_value |
+------+-----------+--------------------+
Is it a good idea to stick an edited column on the x_parent table, indicating to query for x_child rows instead of simply left joining x_parent with it's child records?
Asked by Henry (213 rep)
Jan 9, 2024, 03:49 PM
Last activity: Jan 15, 2024, 02:21 AM