Recursive structure in SQL

In my current project I have to store a hierarchical structure into an SQL database.

I use OpenJPA 2.x as JPA implementation.

The structure is :

• A drawing root, as the name implies, hold the root of the structure;
• A drawing, which is attached to the drawing root, and is the main drawing. The root has only one drawing attached to it;
• Subdrawings, attached to a drawing. They can have subdrawings as well, forming the recursive structure
• Any subdrawing can be attached to more than one parent drawing and can be present in more than one drawing root structure

The last point is important since it adds extra information that we have to store into the database.

Some notes :

• I use DTO’s in the presentation layer, so there’s no problem in having a different representation in the database layer and in the former layer.
• I use separate service and DAO layers

It’s worth to mention that although I’ve kept things slightly simple in this example, in reality there are plenty of other informations linked to each (sub)drawing.

The naive solution

At first - and I must say it’s the current, and thus flawy, solution - I’ve implemented this the naive way, that is, with a foreign key from a drawing to its parent

The drawing root

@Entity
@Table(name = "PDrawingRoots")
public class DrawingRoot {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    private Long id;

    @OneToOne(
            fetch = FetchType.EAGER,
            cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE}
    )
    @JoinColumn(name = "SubDrawingId")
    private Drawing subDrawing;

    // Getters, setters, other data ...
}

The drawing

 
@Entity
@Table(
        name = "PDrawings",
        uniqueConstraints = @UniqueConstraint(
                columnNames = {"BaseReferenceNumber", "DrawingNumber"}
        )
)
public class Drawing {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    private Long id;

    @OneToMany(
            fetch = FetchType.EAGER,
            cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE},
            mappedBy = "parent",
            orphanRemoval = true
    )
    @OrderColumn(name = "Position")
    private List<Drawing> subDrawings = new ArrayList<Drawing>();

    @ManyToOne
    @JoinColumn(name = "ParentId")
    private Drawing parent;

    // Getters, setters, other data ...
}

Of course, since you cannot do JOINS with an unknown depth in SQL, the ORM fetches all drawings one at a time, highly inefficient then.

The real solution

A better solution consists of a table holding foreign key to the drawing roots, the drawings, as well as the parent of each drawings. Furthermore, we add the position - which is important in my context.

 The drawing root
@Entity
@Table(name = "PDrawingRoots")
public class DrawingRoot {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    private Long id;

    // Getters, setters, other data ...
}

 The hierarchy
@Table(name = "PDrawingHierarchies")
@Entity
public class DrawingHierarchy {

    @ManyToOne
    @JoinColumn(name = "Root", nullable = false)
    private DrawingRoot drawingRoot;

    @ManyToOne
    @JoinColumn(name = "ParentDrawing")
    // The one having NULL as parent is the subdrawing of the drawing root
    private Drawing parent;

    @ManyToOne
    @JoinColumn(name = "ParentDrawing", nullable = false)
    private Drawing drawing;

    @Column(name = "Position", nullable = false)
    private int position;

}

So basically we fetch all instances of DrawingHierarchy thanks to a DrawingRoot and then we assemble them in the service layer, looking at the drawing parent (which is NULL for the subdrawing of the DrawingRoot only) and the position.

Caveats

It works pretty well but has better read performance than write, because each time a DrawingRoot is saved, I have to delete all records having a reference to it and then save all drawings, performing a tree traversal.

Do you have some optimizations to this kind of problem ?