[Need to add a "Post #"]

2 minutes ago, requinix said:

Hahahaha

Well, I always assumed - whoever the owner is here - did code this site from scratch.

It's certainly doable.

 

2 minutes ago, requinix said:

Invision Power Board, aka IPB, aka this dumb piece of crap, yes. As it says in the page footer.

I guess if it makes the owner $$$, that is all he cares about.

 

[Help implementing an Entitlement]

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

[Help implementing an Entitlement]

Anyone else?

 

[Need to add a "Post #"]

I thought you guys coded this website yourself?

 

Is this what you are using for PHPFreaks...

https://invisioncommunity.com/buy

[Help implementing an Entitlement]

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?

 

[How much to store in SESSION variable?]

Just now, gizmola said:

MySQL/InnoDB is highly performant which is one of the reasons (along with simplicity) it continues to be used.  All RDBMS's depend on indexing, and the fundamental index type they all provide and implement is the Btree index.  

So, in broad terms, do you think you can tune a small-to-medium MySQL database and have it compete with SQL Server or Oracle?

Or do those enterprise RDBMS have superior internals including how they index?

In the early 2000s, MySL bragging rights were that it was used by companies like Facebook (?), Wikipedia, and some other big ones.

 

Just now, gizmola said:

FWIW, I have extensive experience with Sybase/Sql Server, Oracle and MySQL.  MySQL is unique in that it has alternative engines, as well as forks (MariaDB, Percona). 

If you were starting a business, would you stick with MySQL or go with MariaDB?  (Aside from fears of Oracle doing something stupid, it seems like MySQL is a safer way to go...)

 

Just now, gizmola said:

PostgreSQL is often preferred by people coming from Oracle, as it is very similar down to database objects, datatypes and the stored procedure/trigger language (PL/SQL) that are foundations of Oracle use through the years.  Sybase/SQL Server were also popularized through use of the stored proc/trigger language T-SQL.  Comparatively, MySQL didn't even have Stored Procedures/Triggers until version 5.x beta'd in 2005.

The bigger question in the open source world is not MySQL vs the commercial DB's but rather MySQL vs PostgreSQL.  Even more recently many RDBMS alternatives have come into the mainstream under the NoSQL or Document DB monikers, most notably MongoDB and CouchDB.  This has to do with concerns about scalability, which you don't have reasonable expections will be an issue for you that can't be solved with a bigger server.

Back in early 2000s, PostgreSQL was supposed to be the up-and-coming database.  In the last 10 years t seems to have fizzled.

Last I heard, PostgreSQL was way more mature than MySQL, but that was light-years ago in IT terms.

I would like to switch t PostgreSQL because I figure it might be a way to learn more entreprise-esque database design and coding, but then there is the fear that online forums like PHPFreaks have all but dried up on the Internet, and so you'd be screwd if you needed any kind of support.

There is also the fact that MySQL (and now mariaDB) is the preferred choice of nearly every webhost out there.  (My webhost will help me to some degree with MySQL issues, but I think I'd be hard-pressed to get them to help me with PostgreSQL questions, let alone installing it on my VPS?!

In summary, I am thinking MySQL is my safest way to go, and IF I ever outgrow it, at that point maybe I'll have the $$$ ot resources to choose something more sophisticated?

 

 

[Help implementing an Entitlement]

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.

 

 

[How much to store in SESSION variable?]

Just now, gizmola said:

In phpMyAdmin, when you look at the list of tables for a database the "type" column shows you the engine. 

Ah, right.  Yes, "InnoDB" for all tables in my database, so it sounds like I am safe from a performance standpoint.

 

Just now, gizmola said:

Correct.  For InnoDB tables.  What that means from a performance standpoint, is that if there's a query that uses the primary key index, the entire data for the row gets read in the same operation that searched the index.  I only bring this up since you asked about performance, and is one of the many reasons that MySQL DBA's are fairly unanimous in favoring the use of InnoDB.

So InnoDB is fairly efficient, dare I say "competitive", when it comes to indexing and performance as compared to SQL Server and Oracle, or am I dreaming?

 

[Need to add a "Post #"]

Just now, Barand said:

Right click on the " 22 minutes ago"

Select "copy link"

It looks like my attempt right after posting worked.

I still vote for Post #'s.

 

[How much to store in SESSION variable?]

P.S.  I have a question about how to implement my entitlements.  Should I ask here or start a new thread?

[How much to store in SESSION variable?]

3 minutes ago, gizmola said:

If your structure is normalized correctly you should be fine.

Yes, my database is normalized.

 

3 minutes ago, gizmola said:

Make sure that your tables are using the InnoDB engine.

My project has been on hold for so long, I forget what I used.

How can I tell?  Is there an easy way to tell in phpMyAdmin?

 

 

3 minutes ago, gizmola said:

Any queries that can be satisfied by using the primary key index return all data in the record by reading the index itself.  This is because InnoDB has "clustered indexes" which is a fancy term meaning that the data is actually stored in the primary key index.

You lost me.  All of the record data is stored in the PK index?

 

3 minutes ago, gizmola said:

All primary and foreign keys are inherently indexed.  So the main concern in regards to other required indexes would be queries that have where clauses that aren't keys in the table definition.

This is the single most important detail.  Your queries need to use a relevant index.  An obvious example would be username & password columns in a user table.  Essentially you just need to focus on the WHERE clauses of your queries and make sure that you have an index to handle the query.  For example, if you always query for user by username & password, have an index on username, password, and not individual indexes on username and password.  

Interesting!

 

3 minutes ago, gizmola said:

If you have a monolithic server (webserver, php, mysql running on one box) then you have to plan for contention.  MySQL needs some tweaking to provide a sufficient InnoDB buffer pool allocation.  If it's a small DB as you describe your entire dataset can be in the buffer pool cache the majority of the time.

This is a for a website I am building which is a combination of online newspaper where paid users can post comments and an ecommerce site.

It will sit on a VPS with 8GB RAM, and the whole website and database reside on the VPS.

If I had 10,000 users I would be tickled pink, so I think my website and database will fall on the small side - of course I hope that changes!!

 

[Need to add a "Post #"]

Can I link to my last post a moment ago?

 

[Need to add a "Post #"]

Just now, cyberRobot said:

As mentioned earlier, you could also link to your earlier post. Note the link at the beginning.

Okay, I see what you are saying now.

Does that wok if you just made a post?  (Seems to me that last night I was looking for a timestamp and had just made a post and it said something generic like "Posted Today" but I don't recall...)

[How much to store in SESSION variable?]

Just now, requinix said:

Opinion. Preferably backed by benchmarks. Which then leads into things like "standards" and "best practices".

I am inferring that for a MySQL database/table with less than say 100,000 records, it isn't that big of a deal to run a query, right?

And so if a certain web page/script needed to make even a handful of calls to the database to get things like: "Membership Plan", "Number of Posts", "User Status", etc then that shouldn't really put much stress on MySQL, right?

 

[Need to add a "Post #"]

Just now, requinix said:

Seems pretty easy to me.

Sounds redundant to me.

Why repeat yourself when you can say, "Please see my comments in Post #27"

Having an online forum with posts that have no post #'s is like having a database table with no primary key...

Can this not be fixed?

[How much to store in SESSION variable?]

Just now, requinix said:

Unless you have a particularly bad setup, the user cannot tamper with their session data.

Any good database system will cache data and queries. As long as you're using good database design and have appropriate indexes, it should not be expensive.

Again: good database design and appropriate indexes.

What constitutes "good database design"?

I think my databases are always modeled properly from a "data modeling" standpoint (e.g. logical, normalized, etc).

Not claiming to be a wizard on indexing and performance tuning, but since my database should be modeled okay, and since when this goes live if I had 10,000 users I'd feel like a mega star, I'm hoping things are more forgiving for me.

What say you, @requinix?

 

[Need to add a "Post #"]

10 hours ago, requinix said:

That would be nice. If it were possible to do easily I'd do it right now, but it's likely yet another nice feature they decided (in their infinite wisdom) to remove, forcing anybody who wants it to have to buy some paid plugin.

I suggest quoting yourself.

Quoting myself doesn't solve the issue...

For example, @Barand was helping me out yesterday in the database forum, and we were posting quickly back and forth, and he missed one of my posts with questions.  

By the time I realized that he probably didn't see that post, it was maybe 7 posts up.  And I couldn't merely say, "Could you reply to the post 7 posts up?" because maybe he or someone else was posting as I typed that, thus changing my relative reference.

All that is needed is some *unique* reference to a post, be it a "POST #" or a "TIMESTAMP" - which timezone really doesn't matter @Barand.

 

@requinix, if you meant "Quote yourself and re-post your questions", then yeah I could do that, but doing so would make threads a real mess.

 

It sounds like you are using some COTS software solution?

Either way, YOU GUYS are supposed to be PHP geniuses, so can't you just hack the code and fix things to make the forum more usable?  😉

 

 

[How much to store in SESSION variable?]

10 hours ago, requinix said:

If you store data in the session then you won't look it up in the database, and that means you won't know if it changes. If a user was logged in and you stored that they had a deluxe membership plan, what would happen if that plan changed? Or ended? You would continue using the deluxe plan and the user would have access they should not.

I was more concerned that maybe someone could tamper with the SESSION variable, but you make a good point that I hadn't really thought about.

So how "expensive" is querying MySQL for trivial things like which "Membership Plan" a Member currently has?

My original thinking was that I am sending my script to the database TWICE - 1st to log in the user and 2nd to get their "Membership Plan" - and so maybe that was really inefficient?!

Thoughts?

 

 

[Need to add a "Post #"]

It's a real pain in the ass trying to refer to posts in threads because they do NOT have a unique identifier (e.g. Timestamp, ID).

 

Please add a "Post #" to the forums to help us all to have better conversations and eliminate confusion!!

 

(e.g. "As you can see, @requinix, in Post #27, I did in fact respond to your concern about...)

 

Thanks!

 

 

[How much to store in SESSION variable?]

Currently I am adding the concept of "entitlements" to my website.

In the past, my "article.php" script would simply look to the URL for which article was being requested and then load it.  However now that I am also adding the concept of "premium content" for "paid members", I need a way to control who sees what.

What I am wondering is - from a security standpoint - how much information I should load into the $_SESSION variable.

For instance, right now when a user logs in, I think I just store the "memberID" and "FirstName" and possibly "Username".

It would be more efficient when a Member logs in to also retrieve their "Membership Plan" and store that in the $_SESSION variable, so that as they browse my website, each page can simply grab $_SESSION['MembershipPlan'] and run that through a function that I need to build and then determine if the user gets to access said page.

However, maybe it would be more secure to have it so when a user lands on page XYZ, I would look at their "memberID" and query the database to get their "MembershipPlan"?

Any thoughts on each approach?

Again, my main concern is *security*, but I also suppose this plays into "performance".

 

 

[Handling Plan Versions]

@Barand,

I have to say that this one of the prettiest tables that I've seen!  👍

    +-----------+------------+------------+------------+--------------------------------------------------+ 
    | plan_name | version_no | From       | Until      | features                                         |
    +-----------+------------+------------+------------+--------------------------------------------------+ 
    | Silver    | 1          | 06/01/2019 | 12/31/2019 | AAA, BBB, CCC                                    | 
    | Silver    | 2          | 01/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD                               | 
    | Gold      | 1          | 06/01/2019 | 10/31/2019 | AAA, BBB, CCC, DDD, EEE, FFF                     | 
    | Gold      | 2          | 11/01/2019 | 02/29/2020 | AAA, BBB, CCC, DDD, EEE, FFF, GGG                | 
    | Gold      | 3          | 03/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH           | 
    | Platinum  | 1          | 12/01/2019 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH, JJJ, KKK | 
    +-----------+------------+------------+------------+--------------------------------------------------+
	

 

[Handling Plan Versions]

@Barand

Could you respond to my Q1 - Q5 a couple of posts above?  Maybe you missed it?

(Admins: Would be nice if a given post had a timestamp of # on it so you can reference it!!!)

[Handling Plan Versions]

Just now, Barand said:

No, but every table should have a unique primary key, but not necessarily auto_incremented (for example the plan_version table above, where the primary key is the (plan_id, version_no) combination)

I forgot that question...

So historically, I would do...

PLAN

- id (PK)

 

PLAN_VERSION

- id (PK)

- plan_id (FK)

 

I guess you prefer concatenating keys on child tables?

 

[Handling Plan Versions]

Just now, Barand said:

Your soul would be in mortal peril.

A-ha.  So I guess you would support my general requirement of every table starting with an AutoNumber ID column, eh?

 

Just now, Barand said:

As with so many things

But if you could automate things and let the database check integrity for you, why not?

(Just checked with my website host and cPanel is stuck on MySQL 5.7 so I'll have to wait on true Check constraints.)

 

 

[Handling Plan Versions]

1 hour ago, Barand said:

I prefer to use ids as foreign keys and not data attribute values (such as dates, names etc)

Would it be a sin to use actual dates instead?

 

1 hour ago, Barand said:

The method I proposed for entering new versions would ensure contiguous logical date sequences.

Except that relies on a human not making an error.