CODEWITHSUNDEEP
javasqlpostgresqlhibernatespring-data-jpa
kozyr
kozyr

Reputation: 1254

Table data overrides

I'm currently sourcing some static data from a third party. It's a simple one-to-many, like this

garage:
    id
    name
    desc
    location
garage_price:
    id
    garage_id
    price_type
    price

Sometimes, the data is incorrect, and I will need to correct it. At the same time, I'd like to preserve the original sourced data somewhere and potentially run some queries to show the changes.

My question is whether someone is doing something like this with SQL, Java and Hibernate, and what's the approach you've taken, or would take.

I could add a boolean column, "original_data", to both tables, and before an update happens, run a trigger to copy the row from garage or garage_price into an "original_garage" or "original_price" table as long as original_data is true. Then set original_data to false, and all further updates will just happen on the garage/garage_price tables.

Anything wrong with that approach, and how do people typically work with multiple tables with the same data in Hibernate/JPA? Previously, I'd create a class that holds all the data, and subclass it twice, once per each table, while setting

@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)

on the parent.

Upvotes: 1

Views: 91

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81990

As so often there are various options:

  1. Use Hibernate Envers. It will keep a complete history of changes, so if you do multiple changes each will result in a row in the auditing tables. These tables are separate from your main data tables which might be a pro or a con, depending on your requirements.

  2. Use the approach that you described: Write the original dataset, copy it before modifying it. You'll need two additional attributes:

    A flag marking the original and a technical id do have a unique primary key.

  3. Just as the second version, but you could actually do that in a trigger in the database. Which probably is faster, works no matter how the data gets inserted and to copy rows in the database is actually really easy, while it feels rather cumbersome in Java. Of course, writing triggers is considered a PITA in itself by many Java developers. If your application doesn't usually use triggers and stored procedures it is also really easy to forget about the trigger and being rather confused where these additional rows come from.

Upvotes: 2

Related Questions