Database Design – Best Way to Implement User with Trial Subscription

database-designpostgresql

We have an old database implementation, in PostgreSQL, where users are dependent to an institution subscription, this is based on the renewal_date field of the institution table. Once this has expired users belong to an institution will no longer able to access our Saas webapp. See the image below:enter image description here

Now, we wanted to implement a registration functionality where users are able to register whether their institution has a subscription to our tool. For this, they will be given a 30-day free trial to our tool. So now what I did is to separate the fields that I think can be placed to their own table. See the image below:
enter image description here

After doing some clean up, I realise it would be redundant to implement an expiry_date field in the Account table to store the dates of each user and use this to know when their subscription will end knowing there is an existing renewal_date that we are using to check that. So this is a challenge to me because not every user that will signup has their institution already subscribe to our Saas webapp. Any great suggestion over this problem will be appreciated.

Best Answer

In the first place, renewal_date is more an attribute of Account than Institution; so it's in the wrong table. But even further, I would recommend a Subscription entity that would define a many-to-many relationship between Account and Tools.

(Oh. You don't have Tools. Well, maybe you should. You seem to have only one tool. Can you guarantee that will forever be the case?)

Then renewal_date would properly be an attribute of the relationship between Account and the tool they have access to.

Related Question