Best practice for handling versioned master data?

[Brutus5000]

Even though I have a lot of experience with using Elide on non-versioned master data, I am currently looking for solutions how to handle master data with history.

In general I thought having all the versions of one entity in the same table with an additional column "valid until" (where valid until == null identifies the currently valid record), but having a compound primary key feels very complex.

The requirements would be:

• If you create a new object, it's valid until forever (a.k.a. version 1)
• If you change an existing object, the previous state is being preserved with the "valid until" being set to the current timestamp
• You cannot delete an entity, just flag it for deletion
• Capable to query 1 list of all active/current records
• Be able to see historic versions via relationships

So I see some solutions and I would love to hear if somebody already has experience or some ideas which ones would be best:

• Idea 1: There is one database table containing all versions of the entity (current + historic) and Elide operating on that single table with 1 entity class.
• Idea 2: There is one database table containing all versions of the entity (current + historic) + 2 views (one for current one for historic data) and Elide operating on these 2 views with 2 entity classes.
• Idea 3: There is one database table containing all versions of the entity (current + historic) and Elide operating on that table with 2 entity classes (splitting them using @where clause).
• Idea 4: Using 2 database tables: one for the current entities, one for the historic versions (having the same columns except for the additional valid until) with 2 entity classes.

What I am unsure about is:

• How to express the relationships between the current and older versions?
• How to make Elide create a new version on patch each time
• Compound primary key: How does this work out with one id field for jsonapi (especially in relationships)
• Performance: Anything I need to consider?

[aklish]

I'm not sure about all of your requirements, but have you checked out the Envers project?

https://hibernate.org/orm/envers/