How to version records of table in database?

Question

I have a table in a database that stores products to buy (let's call the table Product). It's necessary to track changes of every product. I have 2 ideas for versioning records.

First is to create a new record in Product by copying the product and override the fields that differ and keep the reference in the record for the older and newer version. In that case a record in Product table is read-only except the field that indicate whether the product is archived or not.

Second is to create 2 tables: Product and ArchivisedProduct. The Product's records are editable, but for each change is created a new record in ArchivisedProduct where differences only are stored (so except an id, all fields are nullable) and tables' fields hold references to each other.

Do you know any tool that could manage that process and works well with Node.js, Prisma, PostgreSQL and Apollo? For such use winston/papertrail was recomended for me, but as I read the docs it seems that it only creates logs.

Exemplary database structure for more clearance: 1st example:

type Product {
  id: Int! @id
  name: String!
  price: Float!
  archived: Boolean!
  productVersionIds: [Product]!
}

2nd example:

type Product {
  id: Int! @id
  name: String!
  price: Float!
  archivisedProductIds: [ArchivisedProduct]! @relation(name: "ProductToArchiva")
}

type ArchivisedProduct {
  id: Int! @id
  name: String
  price: Float
  product: Product! @relation(name: "ProductToArchiva")
}

Answer 1

Depending on how many products you intend to store, it may be simpler to have each Product version stored in the ProductVersion model, and then keep tabs on the latest Product (the "head") in a Product model.

You'd have:

type ProductVersion {
  id: Int! 
  version: Int!
  name: String!
  price: Float!

  @@id([id, version])
}

type Product {
  productId: Int! @id
  headVersion: Int!
  productVersion: Product! @relation(fields: [productId, headVersion], references: [id, version])
}

For each change to a Product, you'd store the new ProductVersion containing the information, and update the corresponding Product to point the headVersion to the new ProductVersion. That would all be part of a single transaction to ensure consistency.

To query your list of products you'd use the Product object and join the ProductVersion. If you store a lot of products and joining is a concern, you con consider having a copy of the whole ProductVersion data in the Product instead of using a relation through the headVersion field.

Note that it also would imply you'd compute diff at runtime, and not have them stored directly in the database itself.