I am coping with user monthly subscriptions.
A person produces a recurring subscription. A lot of things may happen to that subscription, such because it is restored, or it's canceled, or even the latest billing was not successful, or even the user has paused or restarted with subscription.
Would you suggest:
- Getting one
subscriptionsrecord for every user, that has fields for those possible values for example began date, expires date, is active, unsuccessful billing date, canceled date, paused date, restarted date?
- Or getting one
subscriptionsrecord, having a secondary table
subscription_events? A row within this secondary table could record "subscription X continues to be restored". I Quickly would query the newest event for any subscription to determine its current status.
- Or perhaps a better approach?
Don't choose 1. It isn't flexible enough for brand new occasions. You wouldn't want a design where you have to affect the table whenever a new event arrives. You'd likewise need posts for every event date also it just begins getting ugly when you'd like to learn an order of things. Also what goes on whenever a user might have multiple monthly subscriptions?
2 is all about right. To normalize it a little, I'd imagine you've got a USER table along with a SERVICE table having a SUBSCRIPTION mapping the 2 together. Then there'd be a celebration table using the known possible occasions along with a SUBSCRIPTION_EVENT_MAP mapping SUBSCRIPTION to EVENT having a time stamp.
It comes down lower towards the
intention from the
design. Here are a few from many---many---methods to take:
You could utilize a brief history table:
-- stores info re:reason for the last update of a subscription CREATE TABLE subscription_history ( subscription_id INT , change_date DATETIME , change_reason VARCHAR(255) )
Or perhaps a history table along with a research:
-- stores info re:reason for the last update of a subscription -- but links to a change_reason table for reason id lookups CREATE TABLE subscription_history_L ( subscription_id INT , change_date DATETIME , change_reason_id INT ) -- lookup table containing change reasons CREATE TABLE change_reason ( change_reason_id INT , change_reason VARCHAR(255) )
Or perhaps an audit table v1:
-- contains all columns in your subscription table plus audit fields CREATE TABLE subscription_audit ( subscription_audit_id INT -- All the fields from your `subscriptions` table , audit_date DATETIME , audit_reason VARCHAR(255) , audit_by VARCHAR(255) -- example -- etc etc whatever other information that is pertinent to the change )
Or perhaps an audit table v2:
-- this could also act like a history table, so you can change the table name/purpose CREATE TABLE subscription_audit ( subscription_id INT , modified_column VARCHAR(255) , value_old VARCHAR(255) , value_new VARCHAR(255) , modified_date DATETIME ) -- Drawback here is that you'll have one audit record per column -- , and you may have to add extra columns for other data types -- , or convert your values to varchar or something else.. which isn't -- a really good idea! I just want to present this in case you can -- develop the idea you find interesting further
I dunno your RDBMS, but this really is nearly general SQl (that we think I'm able to better use as a means to explain than words and words)