Article Entitlements for Free Articles

[SaranacLake]

Earlier this week i created a table layout to incorporate "entitlements" into my website, which looks like this...

	MEMBER >|-------||- MEMBERSHIP_PLAN -||-------|< MEMBESHIP_PLAN_VERSION -||-------|< ENTTLEMENT >0-------||- ARTICLE
	

 

MEMBERSHIP_PLAN

- id (PK)

-name

 

MEMBERSHIP_PLAN_VERSION

- id (PK)

- plan_id (UK1)(FK)

_ version_no (UK1)

- start_date

- end_date

 

ARTICLE_ENTITLEMENT

- id (PK)

- plan_id (UK1)(FK1)

- version_no (UK1)(FK1)

- article_ID (UK1)(FK2)

 

ARTICLE

- id (PK)

- slug (UK)

- title

- body

 

In order to determine if a Member can view "premium" content, I have decided to....

1.) Take the logged in Member's $memberID and run a query (function) to get their "Membership Plan" (plan_id)

2.) Call another query/function like below to determine if the are entitled to view the "premium" Article...

	SELECT e.id
	FROM membership_plan_version AS mpv
	INNER JOIN article_entitlement AS e
	ON (mpv.plan_id = e.plan_id) AND (mpv.version_no = e.version_no)
	INNER JOIN article AS a
	ON (a.id = e.article_id)
	WHERE ((CURDATE( ) BETWEEN mpv.start_date AND mpv.end_date)
	AND ((mpv.plan_id = $memberID) AND (a.slug = $articleSlug)));
	

 

Now onto my problem...

How can I handle cases where an Article is "free" and therefore everyone is entitled to view it regardless of their "Membership Plan" or even if they are just a Guest??

 

My ARTICLE table obviously has to have *every* Article in it, since that is where the content resides!

But if my PHP script called a function/query like above, then for most of my Articles, there will NOT be a corresponding "Membership Plan" on the left to match to, and so free Articles would never appear.

 

Should I create a dummy MEMBERSHIP_PLAN record called "Free", and also make a dummy MEMBERSHIP_PLAN_VERSION record called "Free v1"?

Or something else?

 

I would say that 60% of my Articles are free and therefore should be accessible to everyone (i.e. Members and Guests), but the other 40% is restricted to various levels of "Membership Plans".

 

Hopefully my design above is still solid and just needs to be tweaked, but I'm not really sure how to tackle this issue.

 

Thanks i advance!

 

Edited February 20, 2020 by SaranacLake

[requinix]

ALTER TABLE ARTICLE ADD COLUMN is_free TINYINT(1) NOT NULL DEFAULT 0;

 

[SaranacLake]

48 minutes ago, requinix said:

ALTER TABLE ARTICLE ADD COLUMN is_free TINYINT(1) NOT NULL DEFAULT 0;

 

I don't see how that answers my question....

Maybe I should have posted in the PHP forum?

I went into greater detail in my last thread, but nobody responded, so I tried summarizing here.

 

[requinix]

Your question is how to handle an article being free.

Your answer is to set up some sort of free membership plan, then add the articles to that plan, and either put everybody into that plan so they have some sort of dual membership thing or to add the free articles to every membership plan that exists.

My answer is to mark the article as free.

[SaranacLake]

Maybe some sample data would help better explain where I am stuck...

MEMBERSHIP_PLAN
+------+-----------+
|  ID  |  Name     |
+------+-----------+
|  1   | Silver    |
|  2   | Gold      |
|  3   | Platinum  |
+------+-----------+



MEMBERSHIP_PLAN_VERSION
+----+---------+------------+------------+------------+
| ID | plan_id | version_no | start_date | end_date   |
+----+---------+------------+------------+------------+
| 1  | 1       | 1          | 2020-01-01 | 2020-02-29 |
| 2  | 2       | 1          | 2020-01-01 | 2020-02-29 |
| 3  | 3       | 1          | 2020-01-01 | 2020-02-29 |
| 4  | 1       | 2          | 2020-03-01 | 2020-06-30 |
| 5  | 2       | 2          | 2020-03-01 | 2020-06-30 |
| 6  | 2       | 3          | 2020-07-01 | 2020-12-31 |
+----+---------+------------+------------+------------+



ARTICLE_ENTITLEMENT
+----+---------+------------+------------+
| ID | plan_id | version_no | article_id |
+----+---------+------------+------------+
| 1  | 1       | 1          | 71         |
| 2  | 2       | 1          | 71         |
| 3  | 3       | 1          | 71         |
| 4  | 1       | 2          | 71         |
| 5  | 2       | 2          | 71         |
| 6  | 2       | 3          | 71         |
| 7  | 2       | 3          | 74         |
+----+---------+------------+------------+



ARTICLE
+-----+-------------------------------+-----------------------------+
| ID  | slug                          | Title                       |
+-----+-------------------------------+-----------------------------+
| 71  | using-indexes-to-tune-mysql   | Using Indexes to Tune MySQL |
| 72  | working-with-arrays-in-php    | Working with Arrays in PHP  |
| 73  | preventing-sql-injection      | Preventing SQL Injection    |
| 74  | file-handling-ing-php         | File Handling in PHP        |
| 75  | using-var-dump-to-debug       | Using var_dump to Debug     |
+-----+-------------------------------+-----------------------------+

 

Let's say that I want Article (id=73) to be available to everyone, including Guests, Paid Members that aren't logged in (aka Guests), and Paid members who are logged in regardless of which "Membership Plan" they have.

 

In my other thread (and above to a lesser degree), I explained how I would take the $memberID and see if a user has an "entitlement" for a given Article.

But since Article (id=73) is free, there are no entitlements.

So how would I change my table structure or my PHP code to handle this?  (Again, I was planning on having a function at the top of my "article.php" script that always looks for the $memberID and then calls my checkArticleEntitlement function.

 

Not sure if my question is making sense?

 

Edited February 20, 2020 by SaranacLake
Updated Table Data

[SaranacLake]

Like most things, there are probably a couple of different ways to solve this problem.

I am trying to write some pseudo-code now, but am also thinking that an outer-join might be helpful...

[SaranacLake]

How about this...

1.) I eliminate my MEMBERSHIP_PLAN_VERSION table because that is overkill for setting Entitlements for Articles.  (This would make more sense when I give Webpage Entitlements.)

 

2.) Take this general coding approach...

	Use-Case
---------
- User clicks on an Article link...
- "article.php" launches.
- Script grabs "Article-Slug" from URL.
- Script checks to see is user is logged in.
- IF ($loggedIn)
  THEN
    - Query for member's "membershipPlanID"
    - Set $membershipPlanID
	  ELSE
    - Set $membershipPlanID = SomeFreeCode
  ENDIF
	- Run this query to see if user has proper Article Entitlement...
	     SELECT e.id
     FROM article_entitlement AS e
     INNER JOIN membership_plan AS mp
     ON mp.id = e.plan_id
     INNER JOIN article AS a
     ON a.id = e.article_id
     WHERE (mp.id = $membershipPlanID AND a.slug = $urlArticleSlug)
	- IF Article is found
  THEN
    Display Article
  ELSE
    Display error "This is a premium article..."
  ENDIF

 

Similar to what @requinix said above, then I would then have to assign all "free" Articles to a "Membership Plan" called "FREE".

 

Thoughts?

 

 

[requinix]

Thoughts? My thoughts are why you're making this so complicated. If the article is free then the article is free. Entitlements has nothing to do with it. Completely and totally irrelevant.

[SaranacLake]

11 minutes ago, requinix said:

Thoughts? My thoughts are why you're making this so complicated. If the article is free then the article is free. Entitlements has nothing to do with it. Completely and totally irrelevant.

 

Actually, I think you are over-simplifying things...

1.) My design is NOT binary.  A "Platinum" Member will have access to Articles that a "Gold" Member might not have access to.

2.) There is also the issue that if a Member is not logged in, then I don't know what Membership Plan they have, so I have to treat them as an unregistered Guest.

3.) Some articles might be viewable by everyone (i.e. Guest, Silver, Gold, Platinum) and other Articles will be viewable by other combinations (e.g. Silver, Gold, Platinum or Platinum-only).

4.) Also is the the fact that i am trying to be efficient with my code and not query the database TWICE - once to see if the article is free and a second time to see which entitlements apply.

 

What was wrong with the solution I posted above? 

(I thought it was consistent with what you said, while tying loose ends together.)

Edited February 20, 2020 by SaranacLake

[requinix]

27 minutes ago, SaranacLake said:

1.) My design is NOT binary.  A "Platinum" Member will have access to Articles that a "Gold" Member might not have access to.

Irrelevant for free articles.

27 minutes ago, SaranacLake said:

2.) There is also the issue that if a Member is not logged in, then I don't know what Membership Plan they have, so I have to treat them as an unregistered Guest.

Irrelevant for free articles.

27 minutes ago, SaranacLake said:

3.) Some articles might be viewable by everyone (i.e. Guest, Silver, Gold, Platinum) and other Articles will be viewable by other combinations (e.g. Silver, Gold, Platinum or Platinum-only).

Irrelevant for free articles (because they're viewable by everyone).

27 minutes ago, SaranacLake said:

4.) Also is the the fact that i am trying to be efficient with my code and not query the database TWICE - once to see if the article is free and a second time to see which entitlements apply.

You shouldn't have to run a query just to see if the article is free: didn't you already run a query to look it up in the first place?

27 minutes ago, SaranacLake said:

What was wrong with the solution I posted above? 

(I thought it was consistent with what you said, while tying loose ends together.)

The solution about setting up some situation with entitlements and something about outer joins?

Let me turn that around on you: what's wrong with my solution?

[SaranacLake]

51 minutes ago, requinix said:

You shouldn't have to run a query just to see if the article is free: didn't you already run a query to look it up in the first place?

When a user clicks on an Article link, I am trying ascertain whether or not they have rights to see the Article BEFORE I go grab the Article from the database.  (Why waste time and resources grabbing an Article is the user is not allowed to see it?  Unless I wanted to show the first paragraph as a teaser maybe...)

 

51 minutes ago, requinix said:

The solution about setting up some situation with entitlements and something about outer joins?

Let me turn that around on you: what's wrong with my solution?

Maybe you missed what I said here...

https://forums.phpfreaks.com/topic/310084-article-entitlements-for-free-articles/?do=findComment&comment=1574685  (Yet another need for a Post #)  😉

(No outer join - that was an earlier post and a bad idea.)

 

 

 

 

[requinix]

1 hour ago, SaranacLake said:

When a user clicks on an Article link, I am trying ascertain whether or not they have rights to see the Article BEFORE I go grab the Article from the database.  (Why waste time and resources grabbing an Article is the user is not allowed to see it?  Unless I wanted to show the first paragraph as a teaser maybe...)

But what if the article doesn't even exist? How can you tell the user it's a premium article when you haven't actually looked for the article?

1 hour ago, SaranacLake said:

Maybe you missed what I said here...

I didn't miss it, I just don't see what additional information it's contributing. You said you want to go through this entitlement process to determine if the article is free, and I said that's a dumb.

[SaranacLake]

42 minutes ago, requinix said:

But what if the article doesn't even exist? How can you tell the user it's a premium article when you haven't actually looked for the article?

 

My use-case above clearly addresses this issue.

Here it is again...

Quote

- User clicks on an Article link...
- "article.php" launches.
- Script grabs "Article-Slug" from URL.
- Script checks to see is user is logged in.
- IF ($loggedIn)
  THEN
    - Query for member's "membershipPlanID"
    - Set $membershipPlanID

  ELSE
    - Set $membershipPlanID = SomeFreeCode
  ENDIF

 

// This checks for the Article's existence AND for entitlement.

- Run this query to see if user has proper Article Entitlement...

     SELECT e.id
     FROM article_entitlement AS e
     INNER JOIN membership_plan AS mp
     ON mp.id = e.plan_id
     INNER JOIN article AS a
     ON a.id = e.article_id
     WHERE (mp.id = $membershipPlanID AND a.slug = $urlArticleSlug)

 

// This decides if the Article is retrieved and ultimately displayed, OR if an error message is displayed.

- IF Article is found
  THEN
    Display Article

  ELSE
    Display error "This is a premium article..."
  ENDIF

 

In summary...

1.) Check for logged in User

2.) Determine "Membership Plan"

3.) Check for Article Entitlement

4.) Display Article or Error

 

What's dumb about that?

 

Edited February 21, 2020 by SaranacLake

[kicken]

The issue I think requinix is trying to point to is that if the article doesn't even exist, you don't want to show a 'This is a premium article.' error, you want to display a proper 404/article not found error.  If you just display 'This is a premium article' in both cases, that will lead to confusion and frustration for your users.

If the article doesn't exist there's no need to do any extra work for determining if the user has a matching entitlement to go with that article so you can cut all that processing out and not waste time on it.

Only if the article exists do you then need to worry about processing entitlements.  Whether or not the article being free involves entitlement checks depends on what kind of solution you end up using for determining free.  If it's just a flag on the article then you can skip the entitlements checks if that flag is true.  If you do some free plan that everyone is a member of by default then you will need to do the checks.

So your logic structure would essentially be best setup as

1. Does article exist?
   • No? Display 404 and quit
2. Is the article free?
   • Yes? Display article and quit
3. Is the user entitled to the article?
   • No? Display entitlement error and quit
4. Display article.

 

Unless you have a compelling reason to do otherwise, I'd probably just start out with requinix's is_free column suggestion to make things easy.  If in the future you decide that's not good enough then you can refactor it into something else.  I spent a few minutes trying to think of a reason not to do a simple is_free column and couldn't really come up with any scenario where more than that would be necessary.

 

5 hours ago, SaranacLake said:

Also is the the fact that i am trying to be efficient with my code and not query the database TWICE - once to see if the article is free and a second time to see which entitlements apply.

Don't nitpick your query count.  One vs two queries will make absolutely zero noticible difference in the runtime of your code.  If two queries make the program flow nicer, use two queries.

 

[SaranacLake]

@kicken,

Thanks for the response.

Last night I sat down and started drawing things out more on paper, and is often the case, I discovered this is more complicated than what I had originally thought.  (I have a bad habit of building gold-plated toilet seat ND toilets AND bathrooms!)  

That being said, I'm not sure what Requinix and I were arguing about is relevant anymore, but I will go back and make sure I don't have the issue that you pointed out, because - in general - I agree that error-messages need to be SPECIFIC - and thus accurate.

I am posting a question in the MySQL forum on how to do  new query that I think I will need.

To be continued, and thanks again!

[SaranacLake]

@kicken,

 

On 2/20/2020 at 11:27 PM, kicken said:

The issue I think requinix is trying to point to is that if the article doesn't even exist, you don't want to show a 'This is a premium article.' error, you want to display a proper 404/article not found error.  If you just display 'This is a premium article' in both cases, that will lead to confusion and frustration for your users.

If the article doesn't exist there's no need to do any extra work for determining if the user has a matching entitlement to go with that article so you can cut all that processing out and not waste time on it.

Don't nitpick your query count.  One vs two queries will make absolutely zero noticible difference in the runtime of your code.  If two queries make the program flow nicer, use two queries.

 

So can I run a quick query to see if the Article exists before moving forward?

For example...

	SELECT id
	FROM article AS a
	WHERE a.slug = $articleSlug;
	

 

Then if the Article exists, I can do a second check to see if the user has permissions to view the Article.

Sound good?

 

Edited February 22, 2020 by SaranacLake

[kicken]

3 hours ago, SaranacLake said:

So can I run a quick query to see if the Article exists before moving forward?

For example...

	SELECT id
	FROM article AS a
	WHERE a.slug = $articleSlug;
	

 

Then if the Article exists, I can do a second check to see if the user has permissions to view the Article.

Yes.  Instead of selecting the ID you could select the is_free field (if you do that) to make the permission check simple in that case.  You could just select the article content too if you wanted.  Unless your going to have really big articles it probably wouldn't make a difference doing it at the start vs after the permission check.  You'd save a query at the expense of more memory usage.

 

 

[SaranacLake]

@kicken,

If you had to guess, do you think there is more overhead in running a query or in the data you retrieve?

In this case, articles will be from maybe 3 - 10 pages.