Help implementing an Entitlement

[SaranacLake]

So I want to implement the concept of "entitlements" on my website for articles, but am unsure of the most efficient way to code things.

@Barand looked at and was okay with my table structure, but my question deal with PHP...

Here is a basic ERD of the relevant tables...

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

 

Up until now, the user would click on an Article link, and my "article.php" script would grab the Category, Sub-Category and Article slug from the URL, and go look for the particular instance of the Article in MySQL.

But now I want to control who can see what - that is only Members at a certain paid level can see "premium" content.

 

Here are my tables...

 

MEMBERSHIP_PLAN

- id

- name

 

MEMBERSHIP_PLAN_VERSION

- id

- plan_id

- version_no

- start_date

- end_date

 

ENTITLEMENT

- id

- plan_id

- version_id

-article_id

 

ARTICLE

- id

- slug

- title

- body

 

And here is how I am thinking of approaching things, but would appreciate your thoughts...

	- Member clicks on a link to the article: "How to use Indexes to Tune MySQL"
	- article.php loads and grabs the category/Sub-Category/Slug from the URL
	 
	**new**
	- call getMembershipPlan( ) which grabs memberID from $_SESSION, queries MySQL , and returns "Membership Plan"
	- call getMembershipPlanVersion( ) which takes the Member's "Membership Plan", queries MySQL, and returns current/latest version
	- call getArticleEntitlement( ) which takes "Membership Plan", "Membership Plan Version" and "Article Slug", queries MySQL, and returns TRUE if this member is "entitled" to view the chosen Article
	**end of new**
	 
	- Take "Category", "Sub-Category' and "Article Slug", query MySQL, and return Article and related metadata
	- Populate Article on page, OR display error: "This article is only available to Premium Members..."
	

 

What do you think about this approach?

 

Is it a sin to have 3 PHP functions and make 3 calls to MySQL?  (This relates to my last thread asking about how much to store in my SESSION variable.

 

 

Edited February 18, 2020 by SaranacLake

[Barand]

Why all those calls? Haven't you heard of JOINS?

One query gets all the stuff that the current version of a plan is entitled to today (query from your other thread)

SELECT p.plan_name
     , v.version_no
     , DATE_FORMAT(v.valid_from, '%m/%d/%Y') as `From`
     , DATE_FORMAT(v.valid_until, '%m/%d/%Y') as `Until`
     , GROUP_CONCAT(f.title ORDER BY title SEPARATOR ', ') as features
FROM plan p 
       JOIN
     plan_version v USING (plan_id)
       JOIN
     entitlement e USING (plan_id, version_no)
       JOIN
     feature f USING (feature_id)
WHERE CURDATE() BETWEEN v.valid_from AND v.valid_until
GROUP BY plan_id, version_no;

 

[SaranacLake]

Just now, Barand said:

Why all those calls? Haven't you heard of JOINS?

Um, yeah!

 

Just now, Barand said:

One query gets all the stuff that the current version of a plan is entitled to today (query from your other thread)

Okay, but that is a REPORT and not exactly what I need in my use-case above.

I've been taught that "component architecture" is preferred in designing an application, so I figured having atomic functions has its benefits.

Also, I'm not sure if I can do everything I need in one query...

First of all, I ONLY want to check if a Member is "entitled" to view an Article first, and if they are not, then I display a message as such.

Second, if you look at my ERD, you'll see a Many-to-One-to-Many relationship which sort of necessitates breaking things up.

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

 

Would the approach I outlined above really be that taxing on my database?

 

[Barand]

5 minutes ago, SaranacLake said:

Okay, but that is a REPORT and not exactly what I need in my use-case above

Add "AND plan = X" to the WHERE clause (where X is the current user's plan) and it becomes less of a report and more a list of what the user can access.

Take it a step further and add "AND article = Y" and you know if a specific article is permitted.

[SaranacLake]

Anyone else?

 

[SaranacLake]

To me, one key question to get a handle on is this...

Let's say you have a database table with 10 columns and 10,000 rows/records.  And let's say you need two pieces of inf from that table.

Scenario #1: You query the table to get row 8519, and grab both pieces of data at once.

Scenario #2: You query the table to get row 8519, and grab one piece of data.  Then after confirming some condition is true.  You go back and grab the second piece of data for row 8519.

On average, which scenario puts more effort/strain on your database?

(It's a contrived example, but I think very relevant to what I described above...)

[gizmola]

1 hour ago, SaranacLake said:

Anyone else?

 

What's missing from your list of tables is the table that describes the relationship between a member/user and the plan version.  

Barand is a SQL expert bar none.  Do what he said with the only caveat being, that the table relating a member to a plan version would be required to determine if a user should be able to access an article.  That 100% should be done with a single query that involves JOINS.  As we discussed previously, with an adequate InnoDB buffer pool, repeated queries will come from the buffer pool cache, so there would be essentially no reading of data by the MySQL server.  

[SaranacLake]

45 minutes ago, gizmola said:

What's missing from your list of tables is the table that describes the relationship between a member/user and the plan version.  

Nope, it's there.  See below...

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

 

At this time, I just have a single field that stores whatever the Member's "Membership Plan" is at any given moment.  (Although I am thinking it might be nice to have a history table of sorts so that I can easily keep track of what different Membership_Plans a Member has over time, but that is immaterial to this discussion.)

A "lookup" table table is always modeled as a One-to-Many where the"One" comes from the lookup table, and the "Many" side is on the parent table (e.g. "Member").

And that was my point above, that - as i recall - it makes it hard to have one query where you join "MEMBER" to "MEMBERSIP_PLAN" plus the other tables.

 

 

45 minutes ago, gizmola said:

Barand is a SQL expert bar none.

Yes, he is very helpful!

 

45 minutes ago, gizmola said:

Do what he said with the only caveat being, that the table relating a member to a plan version would be required to determine if a user should be able to access an article. 

How about this...

When a Member is logged in, I always retain the "memberID" in my SESSION.

So I could have a PHP function called: checkArticleEntitlement($memberID){  }

I would pass in the $memberID which is always known for logged in users, then my query (pseudocode) might look like this...

	SELECT e.id
	FROM membership_plan_version AS mpv
	INNER JOIN 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 ((mpv.plan_id = $memberID) AND (a.slug = $articleSlug))
	

 

Then, if COUNT>0 then that means the Member is "entitled" to view the Article, otherwise I display a gracious message and encourage them to upgrade!!  😄

If they are eligible, then i would query the ARTICLE table for the record where article.slug = $artcileSlug (which comes from the URL)

 

45 minutes ago, gizmola said:

That 100% should be done with a single query that involves JOINS.  As we discussed previously, with an adequate InnoDB buffer pool, repeated queries will come from the buffer pool cache, so there would be essentially no reading of data by the MySQL server.  

 

(This is where a Post # would have been nice...)

 

So to my question above, I asked this...

2 hours ago, SaranacLake said:

Let's say you have a database table with 10 columns and 10,000 rows/records.  And let's say you need two pieces of inf from that table.

Scenario #1: You query the table to get row 8519, and grab both pieces of data at once.

Scenario #2: You query the table to get row 8519, and grab one piece of data.  Then after confirming some condition is true.  You go back and grab the second piece of data for row 8519.

On average, which scenario puts more effort/strain on your database?

(It's a contrived example, but I think very relevant to what I described above...)

 

Put another way, which impacts the database server more....  "Going wide" (i.e. returning lots of fields in a record OR "Going deep" (i.e. having to search a table for a record twice)??

For example, in my SQL above, I chose NOT to return the entire Article record UNTIL I know that the user is eligible to view it, so there I splits things up into two queries.

Thoughts on this, @gizmola?

 

 

[SaranacLake]

No comments on the solution I described in my last post, @gizmola and @Barand?

 

I thought my solution used some of Barand's early comments, but tightened things up.