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

Question

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?

Answer 1

You could omit the child_edited_value and just use a query instead to determine the place from which you want to fetch the data.

SELECT
    xp.x_id,
    xp.parent_id,
    c.child_id,
    CASE
        WHEN xc.child_id IS NOT NULL THEN 'Edited'
        ELSE 'Original'
    END AS record_status
FROM
    x_parent xp
JOIN
    child c ON xp.parent_id = c.parent_id
LEFT JOIN
    x_child xc ON xp.x_id = xc.x_id AND xp.parent_id = xc.parent_id AND c.child_id = xc.child_id;