Handling limits on data for a particular subscription

Question

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?

Answer 1

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:

• tblEntity (call it here TblAccount), with one an exactly one row per entity (account). It holds common info for a given account: AccountId, Name, maybe Login/pawsord, maybe date of expiry but generally little else.
• tblAttribute It holds the definition of an attribute: an AttributeId, a Name, a Type
• tblValues as hinted earlier, with one record for each attribute value assigned to a particular Entity (Account)

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);
- etc.

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:

• the database can accept new attributes at a moment's notice, without any change of SQL's schema, only changes to the application-level schema (mainly the attribute table)
• the database is used to load, upon login, an object representive of an account
• the application uses symbolic names (strings) for designating features of the application (segments if you will) and inquire about the availability of such feature for the underlying account through a business rules layer (allowing yet another de-coupling between the account attribute and the features: without changing the accounts, it is possible to add or remove some features if they can be mapped to one or several account attributes.

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.