Membership systems are used to track lists for clubs, associations, and other groups, including political parties. There is a start to the membership and an end, or rather, a point at which the membership needs to be renewed. Subscription systems, on the other hand, track deliverables, the primary example being for magazine publishers. While subscriptions are sold ostensibly on a yearly basis, what is actually being sold is a number of issues, say six per year or 12 for two years, as an example. For a subscription there is a start issue and an end issue, or rather again, a point at which a subscription renewal needs to take place.
The problem with using dates to track magazine subscriptions is that publishing dates can change and, therefore, are not necessarily fixed as are club or association membership date ranges. The first three major versions of the MA Circulation Manager used dates to track subscriptions because publishers told us that is what they used. Experience, however, told us that to do consistent reporting on revenue, deferred income, earned income and copy liability, we needed to look at the magazine issues themselves. When we switched over from date ranges to issue number ranges there were more than a few irate calls from clients complaining about the change.
Publishers sell magazine issues, not dates, unlike associations or clubs that sell membership by a specific date range. Their earned income is primarily by issue, as is the deferred income. Dates are malleable, meaning that are flexible, and can be assigned based on setting up an issues table to track them. Here is an example of one possible setup for that purpose:
In the example above, it is assumed that you have, or may in the future have, multiple publications, in which case a publication code field (cPub) is used to distinguish them. A unique index is created using the issue number (Issue_No) and the publication code field to ensure that duplicate issue numbers cannot be added to the table. An anticipated publishing date field (IssueDate) is present and set to NOT NULL to force the adding of an actual date. There is a second date field, which is intended to be filled in once the publication is actually mailed (IssueMailed).
The Issue Mailed field, unlike the Issue Date field, can be NULL, as that indicates it has yet to be mailed and is used by stored procedures and user defined functions to indicate the next magazine issue to subscribe to. This could be obtained using a simple scalar user defined function as follows:
Once a mailing for a given issue has happened, then the Issue Mailed field is filled in and the function automatically returns the correct next issue number. For this to work flawlessly, database level rules must be in place to prevent issue numbers from being deleted and enforce that issue numbering is always continuous (known as excluding data islands). This can be done with insert and delete triggers, more information on which can be found at MAI Solutions - Avoid Overlaps.
With a correctly setup database, getting a mailing list for a particular issue number (say issue no. 108) is as simple as specifying the publication and adding a BETWEEN condition operator like this:
This is of course an oversimplification of the selection process for a mailing list for a given publication. Subscriptions can be cancelled, such as for non-payment of fees, or suspended or deferred, say if someone goes on holidays or their mail cannot be temporarily delivered. These kinds of considerations mean that the WHERE clause will be required to be more complicated than demonstrated above. As a result, any complete system must have several stored procedures and user defined functions tuned to the various scenarios possible.
Note that the least of these are functions around implementation of the start and end issue number fields, including the one listed above to obtain the next issue number, and ones do simple issue calculations. One simple function needed is to just return the number of issues the subscription covers, which can be simply done with something like:
This is just one example, with others would including procedures and functions to return:
These are just some of the supporting programming which go to make up a full and robust subscription system. Membership systems have other requirements that revolve round the dates for the start and end, making them fundamentally different then true subscription systems.