Modeling a subscription system, what makes sense to handle status changes?

View previous topic View next topic Go down

Modeling a subscription system, what makes sense to handle status changes?

Post  2by4 on Mon Jun 25, 2012 12:24 pm

All,

Our business is to sell newsletter subscriptions. As we start to model our processes, it seems that we've hit a roadblock to which I cannot find a good answer. Here is what we're trying to do (bear with my newness to dm please):

We receive orders for subscriptions of the different newsletters. A subscription ORDER (fact) is booked, with corresponding order $, dates, etc.
For the typical subscription a variety of things can happen:
Issues are sent, payment is received etc.

The problem is this: SOME of those orders will be cancelled, should cancellation of an order (plus the date of the cancellation) be
modelled as accumulating facts of the original ORDER fact table? Most orders never get cancelled (we hope), so this is a "status"
change that we want to track, when it happens and if it happens. Should we have a cancelled_orders fact table? The trick here is
that an order can be cancelled, but that can cancel can later be reversed if the person changes their mind. In summary we want to
track the 'status' of an order (active, expired, cancelled, renewed) and the dates for which those things happen, but not all of those
things will happen so accumulating facts does not seem like the best choice.

Any insight into this is much appreciated. I'm happy to provide more detail, but I did not want to make my first post overlong...
Thank you in advance,
Franco

2by4

Posts : 5
Join date : 2012-06-25

View user profile

Back to top Go down

Re: Modeling a subscription system, what makes sense to handle status changes?

Post  BoxesAndLines on Wed Jun 27, 2012 9:09 am

There is usually a transacation fact associated with an accumulating snapshot fact. You will capture the cancel in the order status transaction fact and you could capture the cancel status in the accumulating snapshot fact. I would not create a specific canceled orders fact table though. How often a cancel occurs isn't really a concern.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modeling a subscription system, what makes sense to handle status changes?

Post  2by4 on Wed Jun 27, 2012 9:58 am

Thank you, B&L, that seems to make sense. You answered my latent question which was whether I could have the status
in both an accumulated fact form and the individual orderstatus fact table as well.

One other question if I may impose: in my accumulated fact I will have a reference to what amounts to the
'current status' of the order, does it make sense to also have a last_status_change_date in the accumulated
table or should I derive that from the status fact table? I'm thinking the former since it would be very useful
in answering questions off the snapshot such as "when did this person cancel" but I am not sure if this is commonplace
to do.

Thanks again,
Franco

2by4

Posts : 5
Join date : 2012-06-25

View user profile

Back to top Go down

Re: Modeling a subscription system, what makes sense to handle status changes?

Post  BoxesAndLines on Wed Jun 27, 2012 2:17 pm

If statuses come in sequentially then the current status is simply the latest status date. This is true for the transaction or accumulating snapshot. I've never derived the "current" status since there always has been a workflow associated with the statuses.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modeling a subscription system, what makes sense to handle status changes?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum