what is best way to handle history tables in postgresql?

Question

I am working on the following database structure/diagram for an app using postgresql 13 server

Is there a better way to design these table relationships with the tables subscriptions_history and rentals_history to track history of another tables subscriptions and rentals?

Below are descriptions of the tables.

table movies

• id(primary key)
• name
• category
• subscription_price
• rental_price
• rental_period

table subscriptions

this table is used to track a single rental of a specific movies.id. One entry for each movie. So the row gets updated whenever the movie is rented again, thus why we have last_updated. We then track history of rental with the next table right below [table subscriptions_history]

• id(primary key)
• price_paid
• date_subscribed
• subscription_status
• last_updated
• movie_id => foreignkey(movies.id)

table subscriptions_history

this table is used to track the number of times or subscription history of a specific subscriptions.id is renewed

• id(primary key)
• price_paid
• date_subscribed
• subscription_status
• subscription_id => foreignkey(subscriptions.id)

table rentals

this table is used to track a single rental of a specific movies.id. One entry for each movie rental. So the row gets updated whenever the movie is returned or renewed, thus why we have last_updated. We then track history of rental with the next table right below [table rental_history]

• id(primary key)
• price_paid
• rental_date
• return_date
• renew_date
• rental_status
• last_updated
• movie_id => foreignkey(movies.id)

table rentals_history

this table is used to track the number of times or rental history of a specific rentals.id is rented, returned, or renewed for example

• id(primary key)
• price_paid
• rental_date
• return_date
• renew_date
• rental_status
• rental_id => foreignkey(rentals.id)

Is there a better way to design these table relationships with the tables subscriptions_history and rentals_history to track history of another tables subscriptions and rentals?

The purpose of the history table is to log every time there is new rental activity like renting, renewed, returned of a movie for example while the rental table is just showing when a specific movie is rented. A movie can be rented multiple times. And each rental can go through different activities before it is returned. Same concept with subscriptions.

Answer 1

I suggest exploring database normalization and how it drives schema design, and look at how sakila/pagila implements the movie store concept.

As for the proposed design, get rid of the subscription and rentals tables and use one table as your transaction log. A simple query can ascertain the status of any subscription or rental. Duplicating the data in a cache table is asking for trouble from race conditions, user errors, etc.

This fiddle provides an example schema demonstrating this concept.