databaseoracle-databasejpa

How should i solve this persistence relationship problem?

So, my domain is about this: House, Door and Handles. A house has one main Door, a back Doors and some sliding Doors. Doors have handles.

class House {
 Door mainDoor;
 Door backDoor;
 List<Door> slidingDoors;
}

class Door {
 Handle handle
}

class Handle {}

I'm trying to model this both in ORACLE and JPA: I want to reflect the relation as following: If I delete the house, no doors nor handligs are left. On the other hand if I want to delete a door (and ofc its handle), I can, without destroying the house.

How do I reach this relation in databases? I consider the House entity as the parent, Door entity as both a soft entity but a strong one, as without door there is no Handle.

Edit: Handles, not Handlings.

Solution

  • You asked for "both in ORACLE and JPA", so I'm dividing this answer in two parts.

    DB-only

    You can use the ON DELETE CASCADE and ON DELETE SET NULL constraints.

    Use ON DELETE CASCADE to make it so if a House is deleted, its Doors are deleted, and if a Door is deleted, its Handles are deleted.

    Use ON DELETE SET NULLto make it so if a Door is deleted, its reference in the House is nullified, and if a Handle is deleted, its reference in its Door is nullified.

    See examples in the docs.

    For the one-to-many relationship between House and Doors, itcan be solved with an intermediate table, and by having House deletion cascade to the deletion of the entries of that intermediate table that point to the Doors, then cascade to the deletion of these Door.

    Door deletion would cascade to the intermediate table, then set null on House.

    With JPA

    If you want to use JPA to handle the deletion, you can either: