0

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?

1 Answer 1

0

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;
1
  • The problem here is that null does not mean necessarily to use the original. "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"
    – Henry
    Jan 15 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.