Subscription Versus Membership Systems

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.

Subscriptions vs. Memberships ERD

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:

T-SQL Code Sample
CREATE TABLE dbo.tblIssues(
IssueID int IDENTITY(101,1) NOT NULL,
Issue_No int NOT NULL CONSTRAINT chk_tblIssues_Issue_No CHECK (Issue_No>(0)),
IssueDescription nvarchar(20) NULL,
IssueDate date NOT NULL,
IssueMailed date NULL,
cPub nvarchar(4) NOT NULL,
CONSTRAINT pk_tblIssues_IssueID PRIMARY KEY CLUSTERED (
IssueID ASC
) WITH (
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX ak_tblIssues ON dbo.tblIssues (
Issue_No, cPub ASC
) WITH (
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF
) ON [PRIMARY];
GO

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:

T-SQL Code Sample
CREATE FUNCTION dbo.fn_GetNextIssueNo( @cPub AS nvarchar(4) )
RETURNS Int
AS
BEGIN
DECLARE @nIssueNo int = 0;
SELECT @nIssueNo = MIN(Issue_No) FROM dbo.tblIssues WHERE cPub = @cPub AND IssueMailed IS NULL;
IF @nIssueNo IS NULL
SET @nIssueNo = 0;
RETURN @nIssueNo;
END;
GO

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:

T-SQL Code Sample
SELECT s1.nID, s1.FirstName, s1.LastName, s1.Title, s1.Company,
s1.Address1, s1.Address2, s1.City, s1.cState, s1.ZipCode, s1.Country,
s2.StartIssue, s2.EndIssue, s2.cPub FROM dbo.tblSubscribers AS s1
INNER JOIN dbo.tblSubscriptions AS s2 ON s1.nID = s2.nID
WHERE s2.cPub = 'MAG1' AND 108 BETWEEN s2.StartIssue AND s2.EndIssue;

But

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:

T-SQL Code Sample
CREATE FUNCTION dbo.fn_GetNoOfIssues(@StartIssue AS int, @EndIssue AS int)
RETURNS INT
AS
BEGIN
DECLARE @NoOfIssues AS int;
IF @StartIssue IS null
SET @StartIssue = 0;
IF @EndIssue is null
SET @EndIssue = 0;
/* It is assumed that the start issue will always be <= the end issue, so if not a programming error has occurred and needs to be corrected here. */
IF @StartIssue > @EndIssue
BEGIN
DECLARE @TempIssueNo AS int = @StartIssue;
SET @StartIssue = @EndIssue;
SET @EndIssue = @TempIssueNo;
END;
/* It is assumed that issue numbers always start at 1 which allows the following to take place. */
SET @NoOfIssues =
CASE
WHEN @StartIssue = 0 AND @EndIssue = 0 THEN 0
WHEN @StartIssue = 0 THEN @EndIssue
ELSE @EndIssue - @StartIssue + 1
END;
RETURN @NoOfIssues;
END;
GO

This is just one example, with others would including procedures and functions to return:

  • the remaining number of issues to be mailed based on the start issue, end issue and publication code;
  • the issue date based on the publication code and issue number;
  • the last issue that was mailed based on the publication code;
  • the description assigned to the issue number;
  • the start issue to assign a given subscriber’s next subscription based on the publication code and subscriber ID passed in; and,
  • the remaining number of issues to serve (deliver) based on the publication code, start issue and end issue numbers passed in.

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.