For a system where a user can be a member
or admin
, users with the member
role must either pay for it with a recurring subscription, or be given a complimentary access.
My current approach:
user
database table.subscription
table includes a record for a user if they have a subscription.subscription_event
table records each billing or failed payment. I can query this to see if the last event was indeed a successful payment.But how should I record if a user is given "complimentary" access?
complimentary_subscription
with the user ID as the foreign key?subscription
?is_complimentary
and complimentary_expires_date
?expires
column to the user row?Question Review
As @leanne said, you're modeling a Subscription
whose specializations are, say, MonthlySubscription
and ComplimentarySubscription
(to give them a name for this answer).
You know that a subscription can expire:
MonthlySubscription
, that happens when a user didn't pay the current month's subscriptionComplimentarySubscription
, the expiration date is assigned when it’s assigned to the userAs you can see an ExpirationDate
is an essential attribute of any Subscription
, but the way you store it is different in each case. If the first case you'll have to calculate it based on the last event, in the latter you can retrieve it directly.
Dealing with Inheritance in the Data Base
So, to map this sample model to a database schema, you could go with the Class Table Inheritance pattern described in Martin Fowler's Patterns of Enterprise Application Architecture book. Here is its intent:
"Represents an inheritance hierarchy of classes with one table for each class".
Basically you'll have a table with the attributes shared in common between the classes, and you'll be storing attributes specific to each class in a separate table.
Keeping this in mind, let's review the options you proposed:
- Have another table
complimentary_subscription
with the user ID as the foreign key?
Having a separate table for storing ComplimentarySubscription
specific details sound good, but if you don't relate this one with the subscription
table, you can end up with an user that has both a MonthlySubscription
and a ComplimentarySubscription
. Its foreign key should point to the subscription
table, which is the one that tells you if a user has a subscription or not (and you'll have to enforce up to one subscription per user).
- Record a special "subscription" for them in
subscription
?
Yes, you'll have to record that a user has a subscription either monthly or complimentary. But if you're thinking something like recording a special subscription whose amount is zero, you’re looking for a solution that matches your current design instead of searching for the right model for it (it might and it might be not).
- Or add another column to their user row for columns like
is_complimentary
andcomplimentary_expires_date
?
Personally I don't like this one because you're putting information where it doesn't belong. Taking that into account, where you will be storing the expiration date for complimentary subscriptions (remember that for monthly ones you are calculating it, and not storing the expiration date)? It seems that all that information is crying for its own "home". Also, if later you need to add a new type of subscription that table will begin to clutter.
- Add a more general
expires
column to the user row?
If you do this, you'll have to deal with data synchronization each time the subscription_event
gets changed (in the case of a monthly subscription). Generally I try to avoid this data-duplication situation.
Sample Solution
What I would do to favor extensibility when adding new types of subscription is to have the subscription
table to store shared details between MonthlySubscripton
and ComplimentarySubscription
, adding a type
column key that'll let you differentiate which kind of subscription a row is related to.
Then, store details specific to each subscription type in its own table, referencing the parent subscription
row.
For retrieving data, you'll need an object in charge of instantiating the right type of Subscription
given the type
column value for a subscription
row.
You can take a look at the pattern in the "Patterns of Enterprise Application Architecture" book for further assistance on how to define the type
column values, how to use a mapper object to do the Subscription
instantiation and so on.
01/03/2012 Update: Alternatives for defining and handling the type
column
Here's an update to clarify the following question posted by @enoinoc in the comments:
Specifically for the suggested
type
column, could this be a foreign key pointing to aPlans
table which describes different types of subscriptions, such as how many months before they expire without payment. Does that sound logical?
It's ok to have that information in the Plans
table, as long it´s not static information that doesn´t need to be edited. If that's the case, don't over-generalize your solution and put that knowledge in the corresponding Subscription
subclass.
About the foreign key approach, I can think of some drawbacks going that way:
Subscription
to use for each row in the Plans
table. If all you got is a foreign key value (say, an integer) you'll have to write code to map that value with the class to use. That means extra work for you :)Proposed Solution
What I'd do is to put into the type
column in the Plans
table. That column will hold the name of the class that knows how to build the right Subscription
from a particular row.
But: why do we need an object to build each type of Subscription
? Because you'll be using information from different tables (subscription_event
and complimentary_subscription
) for building each type of object, and it´s always good to isolate and encapsulate that behavior.
Let's see how the Plans
table might look like:
-- Plans Table --
Id | Name | Type | Other Columns...
1 | Monthly | MonthlySubscriptionMapper
|
2 | Complimentary | ComplimentarySubscriptionMapper
|
Each SubscriptionMapper
can define a method Subscription MapFrom(Row aRow)
that takes a row from the database and gives you the right instance of the Subscription
subclass (MonthlySubscription
or ComplimentarySubscription
in the example).
Finally to get an instance of the mapper specified in the type
column (without using a nasty if
or case
statements) you can take the class name from the column's value and, by using reflection, create an instance of that class.
A complimentary subscription is a subscription, nonetheless. Its payment amount due and price for subscription would be zero. Its expiration date would be whatever you set as an expiration date for complimentary subscriptions versus regular subscriptions.
You would only need a separate column or table if your current setup wouldn't allow you to successfully query for complimentary subscriptions/users based on those factors.
Note: I'm assuming here that you currently associate a user with a subscription price. You would need to know that anyway in case the subscription price changes in the future, or if you wanted to also enable discounts, say, for recurring or promotional users.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With