How to manage version history for many-to-many relationships using delta in Hibernate?

Question

We are working on a system in which a person can send a document to another person, the document can have several attachments, as indicated below.

Document {
       Set<Attachment> attachments;
}

If X sends the document (Doc1, Ver1) to Y and Y, edits the document, then we must create a new version (Doc1, Ver2) of the document so that the X sent box does not reflect the changes made by Y.

There will be millions of documents in our system in which each document can have hundreds of attachments. Also, a document can travel through n people for a certain period of time.

We should be able to download any version of the document along with a list of attachments that were at that time, so I need to support versions of the document, so the table structure immediately appeared, which was lower.

Document - id primary key, ver_id primary key
Attachment - id, doc_id foreign key, doc_ver_id foreign key

But the problem with this structure is that if X sends the document to Y with 100 attachments, and Y made some minor changes, then I need to create a new version along with copying all the attachments for the new version, most of which will be the same as in the previous version, since we will have millions of documents, and each document will be moved by n number of people. This model will lead to a very large application table with a lot of redundant data.

So, we thought of an alternative structure for an investment, as shown below.

Document - id primary key, ver_id primary key
Attachment - id, doc_id, attached_ver_id, detached_version_id

Hibernate Document , , - , , .

Answer 1

, :

1 - "": , .

... ...

2 - "": . .

/: 1, , , , . 2 . (, , 1 , , ).

?. , 1, 2.

Hibernate Document , , - , .

- , , Hibernate . Document Version Attachment:

Document - id primary key, ver_id primary key, ...
Attachment - id primary key, ...
DocumentAttachmentDelta - doc_id, doc_ver_id, attachment_id, added_or_removed_flag

DocumentAttachmentDelta - @ManyToOne, , . , , . , , , , .

( )

@Entity
class Document {
    /* ...other fields... */

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "document", orphanRemoval = true)
    List<DocumentAttachmentDelta> documentAttachmentDeltas;
}

@Entity
class Attachment {
    /* ...other fields... */

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "attachment", orphanRemoval = true)
    List<DocumentAttachmentDelta> documentAttachmentDeltas;
}

@Entity
class DocumentAttachmentDeltas {
    /* ...other fields... */

    @ManyToOne
    Document document;

    @ManyToOne
    Attachment attachment;
}

Answer 2

, .

@Entity
public class Document {

    @Id
    private String id;

    @Lob
    private byte [] firstVersion; 

    @OneToMany(mappedBy = "document")
    private final Set<Attachment> attachments = Sets.newHashSet();
}

@Entity
@Table(uniqueConstraints = {
        @UniqueConstraint(columnNames = { "document_id", "version" })
})
@Check(constraints = "(delta is null and previous_version_id is null) or (delta is not null and previous_version_id is not null)")
public class Attachment {

    @Id
    private Long id;

    @Column(nullable = false, name = "version")
    private Long version;

    @Lob
    @Column(name = "delta")
    private byte [] delta;

    @JoinColumn(name = "document_id")
    @ManyToOne(optional = false)
    private Document document;

    @JoinColumn(name = "previous_version_id")
    @ManyToOne(optional = true)
    private Attachment previousVersion;

}

, , . , . ( ), -- / . , ​​ .

, ( Postgres, ), , , . Hibernate.

, , , Document. "" , , (. ).

, , :

@Entity
public class Document {

    @Id
    private String id;

    @OneToMany(mappedBy = "document")
    private final Set<Attachment> attachments = Sets.newHashSet();

}

@Entity
@Table(uniqueConstraints = {
        @UniqueConstraint(columnNames = { "document_id", "version" })
})
public class Attachment {

    @Id
    private Long id;

    @Column(nullable = false, name = "version")
    private Long version;

    @Lob
    @Column(name = "content")
    private byte [] content;

    @JoinColumn(name = "document_id")
    @ManyToOne(optional = false)
    private Document document;

    @JoinColumn(name = "previous_version_id")
    @ManyToOne(optional = true)
    private Attachment previousVersion;

}

, previous_version_id NULL, , .

, . ( ) , . , - .

Answer 3

Document Attachment @ManyToMany Java, ver_id.