What would be the best way to limit an account based on a particular subscription? For example my Basic plan might allow a subscriber to create a maximum of 2 user accounts and have up to 50 customers; the Premium plan might allow 10 users and 200 customers.
The only way I can think of to handle this would be to create a
subscription_plans table that defines the limits, for example:
id description monthly_price max_users max_customers
1 Basic 40.00 2 50
2 Premium 60.00 2 200
and use some kind of join table to relate these to the accounts. This doesn't sound like the best way since I would regularly have to query the number of users/customers/whatever (maybe on each page, although I suppose I could use caching) so I can warn the user if they're approaching their limit. Also I would need an easy way to let the user "self service" their account if they need to upgrade. I plan on having the actual subscription management and recurring billing handled by a third-party service.
Is there a better way to handle this?
The User Account database is a perfect candidate for an Entity-Attribute-Value (EAV) schema!
It is typically small enough (no concerns with one EAV's weakness i.e. performance/scaling) and user account configurations are typically the subject of a never-ending changes of packaging and rules.
The alternative could be a object database, whereby the object instances of the model described below are "hibernated" to storage. The down side of this latter approach is that it may be more difficult to work with the accounts database as a whole.
A bad choice, certainly, would be a truly relational schema (as hinted in the question), with its records more less directly exposed to the application level.
EAV in a nutshell
The data is stored "vertically" rather than "horizontally". In the example of the question, for customer id 1 we'd have 4 records in the value table, one each for description, montly_price, max_users and max_customers. When and if we need more attributes (say one aimed at providing access to advanced/premium features, for example) some of them possibly with multiple values, we just define them in the attribute table, and add value records in that same table.
In more details:
The database structure is typically made of 3 tables:
Tying in with the program logic:
Typically we introduce an object model whereby an account is little more than a few "header" properties (Id, name), and the various attributes are stored in Key-Value pairs of a hash (dictionary). These objects, or a few utility methods, provide a layer through which all inquiries and actions about an account are handled for example:
- bool CanUserAccess(FeatureName); // evaluate the rule associated with FeatureName (typically a series of tests on presence and/or value of attributes for that account) and returns true if the account can a-priori access said feature (another layer may prevent such access at a given time, because of concurrent number of users or other...)
- int TakeLicenseFor(FeatureName);
And that generally covers it. Lots of details remain to be delt with, but the tentative architecture proposed above offers an overall loose coupling of the various elements of the puzzle:
With 2 layers of insulation, the application won't be troubled even if the physical layout of the database is changed or if some business rules come and go. There is of course a tie-in in the application: the references to these "feature strings", but these references can typically be placed high (early) in the scripts, allowing a very visible and easily changeable mapping between the application's pages/features and the account management logic described.