2

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.

0

1 Answer 1

2

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.

8
  • so how do i display history of a rental for example...so for a single rental that went through multiple events like renewed 2 twice for example before then finally getting returned? what i want to do is use one table to track the single rental of a movie and the history table to track the history of the rental...got it?
    – uberrebu
    Commented Aug 29, 2021 at 9:07
  • this is a very special use-case...where a single row in a table can go through different events over time in another table, which am calling the history table...not many databases or db designs use this particular use-case..so please think this through again
    – uberrebu
    Commented Aug 29, 2021 at 13:14
  • Looking at your proposed schema, the key (the attributes that uniquely identify one event) for rentals is {movie_id, rental_date}. The transaction log would have entries for the initial rental, any later renewals, and return. You can use this key to find all events in the transaction log that occurred for this rental. If it would help, I can put together a fiddle demonstrating the concept.
    – dwhitemv
    Commented Aug 30, 2021 at 2:10
  • sure please...this is a special case that i dont see in public ERD...so really want help from DBA experts willing to help...will appreciate the fiddle...thanks
    – uberrebu
    Commented Aug 30, 2021 at 3:31
  • I have added a fiddle to demonstrate the concept.
    – dwhitemv
    Commented Aug 31, 2021 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.