SaranacLake

Well, I always assumed - whoever the owner is here - did code this site from scratch. It's certainly doable. I guess if it makes the owner $$$, that is all he cares about.

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

Anyone else?

I thought you guys coded this website yourself? Is this what you are using for PHPFreaks... https://invisioncommunity.com/buy

Um, yeah! 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?

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

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.

Ah, right. Yes, "InnoDB" for all tables in my database, so it sounds like I am safe from a performance standpoint. 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?

It looks like my attempt right after posting worked. I still vote for Post #'s.

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

Yes, my database is normalized. 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? You lost me. All of the record data is stored in the PK index? Interesting! 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!!

Can I link to my last post a moment ago?

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

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?

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?

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?

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? 😉

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?

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!

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

@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 | +-----------+------------+------------+------------+--------------------------------------------------+

@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!!!)

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?

A-ha. So I guess you would support my general requirement of every table starting with an AutoNumber ID column, eh? 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.)

Would it be a sin to use actual dates instead? Except that relies on a human not making an error.