Approach for store multiple relationships POO objects in relational database

Question

I'm developing a system in which a ClassA object can have multiple ClassB and ClassC objects.

And for store these objects in a relational DB model I create the following tables:

This is a simplified example of the real system.

My problem arises when I need to update ClassA objects because I need to check if the modified object has more ClassA and ClassB objects or less and then update the ClassA table and add, update or delete corresponding rows from ClassBObjectsPerClassAObject and ClassCObjectsPerClassAObject tables. So my approach was to keep the original object unmodified in the persistence layer until the modified object comes back from the interface and then compare them, but with this approach, all objects need to be duplicated. Is there a better approach, method, or design pattern that I can use for this kind of problem?

I'm using Python with SQLAlchemy and MySql in the persistence layer, flask in the services layer, and Angular in the frontend

Answer 1

Considering that these are one-to-many associations (as confirmed in the comments), it appears that your relational schema could be simplified, getting rid of intermediary association tables.

Using a simpler foreign key mapping would mean that the tables for classes B and C could each get the foreign key referring to class A's id. THe RDBMS allows bidirectional navigation. Keep the association tables for many-to-many associations.

Then for the modification, it should not be an issue either, thanks to the isolation levels in the database: if you update an A, just do not commit immediately: do the related updates that are needed and commit once you have a consistent state (or rollback if yu cannot complete your transaction; your app would then have to reread the initial state of the A from the DB). The Unit of Work pattern could help you to deal with these kind of consistency issues.