Database Schema - User Subscriptions

Question

I have a subscription model in my database that subscribes users to the courses for certain months. There are few types of assignment. User can:

1. Purchase course x month subscription with Credit Card
2. Purchase course x month subscription by entering some Activation Key
3. System administrator assign user to x month subscription.

If user renew his/her subscription I’m not deleting the old one, I’m creating a new record in the subscriptions table and keep old one as a history, also if the same subscription is updated by administrator I’m creating new record again. User’s current subscription is identified by the last record. The expiration date for the user to course is the date that is displayed in the latest record. For example if you check the attached screenshot.

User 1 has three subscriptions and one of them says that course must be expired in June 15, however the real expiration date is June 14, because this is the latest record for this user - course combination.

The reason why I keep all these records (and not just updating the existing user-course expiration date) is that I need this history. If something go wrong, I’ll always be able to recover where a mistake was.

I think you’ve got my idea, my question is, if this is a good solution and how can better implementation idea look like.

Thanks for your time.

Answer 1

Here are the options that I see.

1. add new records every time a subscription date is changed. The benefit is one table to deal with subscriptions and less writes to the DB as opposed to #2 but a lot more records that are not necessary in the table

2. Add an audit table that maintains a history of changes for accountability purposes and edit the subscription table rather than add new records every time there is a change. Here you have 2 writes to the DB but you have a cleaner subscription DB and a separate table for auditing (when/if necessary to inspect)

Anyone know which is better or any other suggestions?