SaranacLake

@Barand, Asked earlier... Q1.) So I was thinking of... MEMBER >|-------||- MEMBER_PLAN -||-------|< PLAN_VERSIONS -||-------|< ENTITLEMENT >|------||- FEATURE And, as you were saying, when I need to determine which Features/Webpages to display for a given MEMBER, I would just query the latest PLAN_VERSION for whichever PLAN they were assigned, correct? Q2.) If I allow grandfathering on prices, then I would want to leave End_Date either blank or make it some insanely high value like 9999-12-31, correct? Actually I am thinking it might be harder than that, since I am dealing with subscriptions that need to be renewed, so I think I need to consider that more as I get back into finishing a design for subscription... Q3.) What are your thoughts on MySQL 8's check constraints? Are they very mature compared to other databases like SQL Server and Oracle? Q4.) Is MySQL v8.0 out yet? And if so, is it stable? Q5.) What database object/component/whatever would I need to ensure that when adding a new record that the Start-Date > End_date of the previous record? I'm think that would require a Trigger and some coding...

@Barand, Why is that? All NOT NULL does is ensure you have a date value, it doesn't ensure that overlapping dates will not occur. But is there a way in MySQL to add a "constraint" to prohibit invalid dates? (Last I knew, MySQL did not offer Constraints, but I thought they were going to be introduced at some point.) Or could I create a Trigger that checked to make sure End_Date > Start_Date and a second trigger to make sure Start_Date > End_Date on the previous record? Right, and I was asking f you think that is a good idea. (To me, having a history of something is why databases exist?!) That is what i was thinking. So I was thinking of... MEMBER >|-------||- MEMBER_PLAN -||-------|< PLAN_VERSIONS -||-------|< ENTITLEMENT >|------||- FEATURE And, as you were saying, when I need to determine which Features/Webpages to display for a given MEMBER, I would just query the latest PLAN_VERSION for whichever PLAN they were assigned, correct? If I allow grandfathering on prices, then I would want to leave End_Date either blank or make it some insanely high value like 9999-12-31, correct? Actually I am thinking it might be harder than that, since I am dealing with subscriptions that need to be renewed, so I think I need to consider that more as I get back into finishing a design for subscription...

@Barand, You never cease to amaze!! First off, how in the world do you get such nicely formatted (1970s) tables here in the forum??? It looks like you agreed with my high-level design, and from what I read, you were just nice enough to flesh out the columns and keys for me - thanks! Now for some follow-up questions... Q1.) I had envisioned using dates to form the primary key for PLAN_VERSION, but it looks like you decided to disregard intrinsic values and just go for an AutoNumber? What are the pros and cons of your approach to what I had envisioned? One benefit to my approach would be ensuring that dates made sense and weren't Nulls. Q2.) What are my options with MySQL to ensure that dates entered are logical? Let's say I have this real data... - Gold, 1/1/2019, 12/31/2019, v1 - Gold, 1/1/2020, 2/29/2020, v2 - Gold, 3/1/2020, 9/30/2020, v3 Q2a.) Can I create a constraint in MySQL that says, "End_Date must be greater than Start_Date"? Q2b.) Can I create a constraint in MySQL that says, "For a new record added, the new record Start_Date must be greater than the most most recent record's End_Date"? Having such logic in my table would ensure that each PLAN_VERSION record has logical dates. Q3.) Is there a problem having a PLAN -||------|< PLAN_VERSION design? I want it so I can keep track of how things evolve as I add or remove features. Q4.) Related to #3, would this design cause problems for MEMBERS as a PLAN evolves? My thinking is this... If I add a new FEATURE to my website (e.g. webpage, functionality) within a given PLAN, then I presumably want to offer any new Features to all Members of that plan, right? If you say, "Yes", then that would mean that if a new PLAN_VERSION comes out, then I'd have to update every MEMBER record to reflect going from say "Gold v1" to "Gold v2", correct? By contrast, for PRICING, I am thinking of offering "grandfathering", and if a person renews their membership, then I will keep them at the same price (e.g. $50/year), but for new people signing up, they will have to pay the inflated price (e.g. $55/year). But back to my earlier question, it seems like while increasing prices on new members makes sense, making sure everyone gets the latest PLAN_VERSION also seems the way to go both from a fairness standpoint, but also from a maintenance standpoint. Agree or disagree? (This of course would impact my data model!) Thanks!

I am trying to model "Plans" and "Entitlements" for my website, and could use some help on adding "Versions"... For my website, "Members" can purchase various "Plans" which in turn give them different levels of access. And now I want a way to track how a "Plan" might evolve over time. Here is what I have so far... PLAN -||------|<- PLAN_VERSION -||------|<- ENTITLEMENT ->-------||- WEBPAGE PLAN - Silver - Gold - Platinum PLAN_VERSION - Gold, 1/1/2019, 12/31/2019, v1 - Gold, 1/1/2020, 6/30/2020, v2 - Gold, 7/1/2020, 9/30/2020, v3 - Gold, 10/1/2020, 12/31/2020, v4 ENTITLEMENT Gold v1 Feature-01 Gold v1 Feature-02 Gold v2 Feature-01 Gold v2 Feature-02 Gold v2 Feature-03 Gold v2 Feature-04 **I added the "version" above to show what I am trying to do, but as you can see from my question below, I would actually use that for the key, and would prefer using a Start and/or End-Date. Questions: 1.) What should I use for my Primary Key in the PLAN_VERSION table? Plan-Name + Plan-Start-Date ? Plan-Name + Plan-Start-Date + Plan-End-Date ? Something else? 2.) What can I do to prevent the Plan-Start-Date and Plan-End-Date from getting mixed up between different records in PLAN_VERSION? For example, you wouldn't want to allow this... - Gold, 1/1/2019, 12/31/2019, v1 - Gold, 12/5/2019, 3/1/2020, v2 - Gold, 1/1/2020, 9/30/2020, v3 3.) Any other suggestions on setting this up? Thanks!

@gizmola, Thanks for the thoughts. All of this is definitely a journey, because as I sketch things out (and talk to people) I learn new things and that can change my design. Dealing with the unknown and not knowing how to design things until you successfully design thing is what makes all of this a challenge! I'll take what you and others have said above and try to come up with a design that is flexible, scalable, and that helps me retain customers and make $$$. Thanks.

Yes, for existing members that is true. I guess one of the earlier topics of debate is whether I should do that for a new member. I am still kicking - no pun intended! - things around, and sketching things out and doing use-cases which help me to formulate a 20,000 foot view. But as of now, it makes more sense to me to wrap up account creation and payment processing into a single transaction. If they both succeed then the new member is on his/her way. And if things fail - more likely the payment - then I will simply route them back to the Checkout page and auto-populate non-PII fields to help them out. t complicates things more than it's worth creating an account for a new member without the payment in hand because of things like "Trial Offers". You can't get a trial offer (e.g. 30 days for $1) if you are a member, and I don't want to have to write a bunch of logic to check if a Member is not yet really a member because their payment puked. (It's a judgment call.) But I'm still kicking - there's that word again?! - the tires. 😉

Being a guru on PHP does not make one an expert on UI/UX. When you, or anyone else, has extensive *published* research on the topic of optimizing e-commerce checkout, then you can talk smack. Until then, stick with PHP. Sorry, I'll trust what industry experts with published research/journals/books over a single person's opinion. And on this particular topic, requiring a new customer to first sign up for an account, and then activate the account, and then come back and browse your account and hopefully checkout is a guaranteed way to lose a significant amount of your customers. Of course, that isn't what this thread is about, so please take your hostility elsewhere.

The *new* user enters in a username, email and password and enters payment details, clicks "Process order" and the payment fails. How would I securely log them back in? (I don't think there is such a thing - log in needs to always be done by the user!) I could display an error message, "Oops! Your payment was declined. <<Cancel>> <<Try again>>" If they click <<Try again>>, I could reload the Checkout form, and use session data to populate the form MINUS the password and payment details, and make them try and checkout again. Again, after extensive research, all of the experts say that is an outdated approach that will lose you business, because people are impatient. Remember: I am talking about *new* customers here (i.e. registering and checking out for first time). if you are an existing Member, then the flow follows a more traditional e-commerce approach: Login, shop, purchase. And now maybe you see why the easiest option for *new* customers who have a failed Checkout, is to simply roll back everything, and make them try again. Although as I mentioned above, I could certainly try and make that easier by auto-populating as many fields as possible minus things like password and payment details. I guess I haven't really paid attention or had this happen, but if I went to a site that sold subscriptions and the checkout failed, I am 95% certain I'd have to start all over since they don't have an account on file for me...

@kicken, Since it sounds like my approach is okay to you so for (i.e. I allow people to have accounts without an active subscription), then what is the best way to handle the following scenario... A first-time user of my site - after surfing around for free for a while - decides to purchase an annual subscription for $50. During checkout, they Step 1: Enter Account Details, Step 2: Sign TOS, and Step 3: Enter Payment Details then click "Process payment". My code successfully creates "Member", "TOS", "Order" and "Order Details" records in the database, but the payment processor sends back a "Fail" message because they didn't have enough available credit. As the website owner, only 1 of 3 things got completed that I need completed (i.e. Member was created). I also need them to pay me, and I need them to "activate" their account by clicking on a link that I will send them. (Standard procedure.) So what is the smoothest way to handle this interrupted checkout? Scenario #1: I don't tell them (immediately) that their payment failed. Instead, I display, "Your account has been created. Please check your email and click on the activation link to start using your account." Then after they do that, THEN I point out that their payment failed and they will need to go to the "Store", choose a paid subscription plan, and try checking out again. Scenario #2: I display a message t the effect: "Oops! Your payment didn't go through. Please log into your new account, and try purchasing a subscription again." (After I get a payment, I can worry about reqiring them to activate their account.) Scenario #3: Maybe there is a less "disruptive' way to allow them to try checking out again? I'm not really sure from a UI/UX standpoint what is the most gracious way to handle this scenario... 🤔

I don't know... Maybe there isn't a problem? I just haven't gotten to later design considerations like entitlements and how to handle re-subscribing - which I know is the most challenging part of all of this. Put another way, I sorta need to figure out "A" before I can design "B", but as we are discussing here, I sorta need to understand "B" before I can finish "A". That is why I find building all of this so challenging! Okay, it sounds like I am still on track, I just need to think some of my design out some more, and probably circle back and refactor some parts like we are discussing here. Thanks! I guess originally I wanted a "cleaner" registration process with no unfinished business. However, it might not be uncommon that a user genuinely wants to become a Member and subscribe, but maybe they thought they had available credit and didn't. And to @requinix's point, it is better to capture any data and not make people re-enter things. So if the payment failed, I could just route them somewhere and ask them to try another payment method. This is still hard to get my head around since there is the whole "activation" part. I guess if creating the Member succeeded, but the payment failed, then I could let them activate the account, but not give them any entitlements, and either befoe or after (??) they activate the account remind them that they still have to pay before they can use the website. Maybe once I get to the entitlement part this will fall into place? Lots to still consider! I agree. Possibly, but one thing I am adamant about is that if you want to create a profile or post comments or send IM's, then you need to be a paid Member. And I am unyielding on that so it creates a cleaner community where I don't have to deal with trolls all day - of if a troll joins, I make a quick $50 and they are gone! So for that reason, I don't see an "in between" option. True, but I hope to make the free part so enticing, plus the level-of-entry so low (i.e. "Hey, this isn't a lot of money, I think I'll give it a try!"), that people will quickly subscribe, but time will tell...

But do I? See my response below... Right now I am trying to figure out all of the logic of membership/subscriptions/e-commerce, so pardon me if I misspeak. In the scenario above, when a paid Member's subscription expires, they would (obviously) retain their account, and they would be able to still log in, however all entitlements would be removed, leaving them only with the option to do things like reset their password or change their email. Anyone can got to the "Store" and purchase a subscription whether they have one of not. So for the case where a Member's subscription had expired, they would have the ability to log in, go to the store, purchase a new subscription, and then their entitlements would be restored. I think this addresses your valid concern? What is complex is dealing with Memberships and Subscriptions. (Selling physical products is much easier as far as logic goes.) Well, I am doing that, I just haven't written the logic for that. Each Membership Plan comes with certain "Entitlements". So, for example, a "Silver Plan" might give you an "entitlement" to Post Comments, but a "Gold Plan" might give you the ability to Post Comments and Listen to Podcasts. So WHAT you can do on my site is a function of WHICH Plan you purchase as each Plan has certain Entitlements. But what I didn't build into my design - at this point - was the ability (or need) for people to create an Account without also purchasing something. (And while I appreciate @requinix's thoughts, I'm not sure I agree that I need that middle tear - either people are serious to subscribe for $50/year of they likely never will!) So I think what I am doing is consistent with you advice, @kicken, right? One reason I am bundling up Registration and Checkout is because I did *extensive* research on UI/UX design, and all of the experts say that you need to bundle Subscribing and Paying in ONE form/page/action or you risk losing people. (Which was tough medicine to swallow since I spent a shitload of time building a registration modlue that mimicked the sites like PHPFreaks work, only to find out that approach is "outdated".) So when a user chooses the "Gold Plan", I take them directly to checkout and ask them to set up an account, sign the TOS, and enter payment details all in one shot - per what the experts say to do. Now I could offer people to "register" before they buy something, but to be honest I think that is pussyfooting and shows a lack of confidence in your product/service. The Wall Street Journal's stance it "pay up or don't read our damn paper!" And at least I am giving people maybe 30% access for free and without needing an account. And if THAT isn't enough to entice people to get a paid Membership, then they aren't the people I am chasing Which basically leads me back to where I am in my OP of doing Step 1: Account Deatils, 2.) TOS, and 3.) Payment Details during Checkout, AND I wasn't quite sure if wrapping that in a huge transaction was the best way to go. @kicken, do you agree with @requinix that I need a "middle tier" of registered users who pay nothing? (That is something I was trying to avoid based on years of experience online and seeing what pays off and what doesn't.)

That's a pretty major paradigm shift in how I designed things. Where were you a year ago when I was trying to figure this all out?! *LOL* So why is that middle tier so important? It's not like I am asking people to make a huge jump. (Either you have $50/year for a subscription or you aren't interested or are cheap.) And I have enough "teasers" on my site that, in my opinion, it shouldn't be hard to get people to go from anonymous visitors to paid member. Do you have examples of companies where your suggestion would be clearer? I get that you are saying I need another step, but I'm just wondering why that is so critical as you claim. In my mind, going from $0/month to $4/month isn't a huge commitment. Also, a lot of my website/business is based on what other (successful) companies already do with free vs paid content (e.g. New York Times, Wall Street Journal, Time, etc.) Of course I am always open to new ideas. Does PHPFreaks do it differently? Most models I have seen is "Free" or "Paid". And to be clear, I *DO* offer different types of paid memberships, so it isn't as "black and white" as you describe. I haven't set prices, but am thinking of starting off with: $0/yr, $30/yr, $40/yr, and $50/yr.

Well, I do have the proper order, it's just feel like this has to happen all at once, and you're not so concerned about that. I guess I could make it so an account can exist but the user can only log in, but nothing else. Again, juts seems like more complexity than I was looking for. Allow me to clarify... Everyone can visit my website without an account and view maybe 30% of the content but really with no real features (e.g. posting comments, sending IM's, etc.) So I am doing what you advise above. But the difference is that to do those basic things (i.e.. "try it before you buy it") you do NOT need an account. That's the architecture I went with. However, to be able to really get to the good stuff (i.e. the other 70% of the content and ll of the cool features I built), then you need a PAID account - because you either are a "visitor" with no account, or a PAID Member. Which is why I am sorta nervous about the Checkout process going smoothly... The wish is that you decide to pony up for a paid account, you choose a particular paid plan (e.g. Silver, Gold, Platinum), next you enter account details (e.g. username, email, password), then you enter payment details, and finally click "Process Order" and an account is created as soon as I verify I got your $$$. What I want to avoid is that I create an account for you but didn't get your $$$, or I took your $$ up front but I couldn't reciprocate and give you a working account. I guess I could set up entitlements so you could in theory have an account that you can log into, attempt to re-purchase stuff, but is crippled from being able to access anything, but that just seems like more complexity - and I already feel like I am designing a website above my skillset?! Just to clarify, if I simply wrapped the Member creation part (#1-#4) along with the payment part (#5-#6) in a transaction, is there a sin in that? I believe that would cover my concerns about being stuck with half-a-transaction. Can you comment on that? Also, you seemed to laugh at my "10 concurrent users" comment. And you mentioned things like duplicate emails. I think I have that covered, so is that to say that the chances of things puking - whether I wrap all of this up in a transaction or not - is slim? Like I said above, I have really bitten off a huge chunk here, but the thoughts of running my own side company is a dream of mine! Thanks.

@requinix, First off, to be clear, what makes this complicated is that as user is giving me $$ for website/content access. So things sorta need to happen all at the same time. (If this was just buying socks, it would be MUCH easier!) Well here are some scenarios I am worried about... If I create the Member records, and the payment fails (e.g. user is over limit), not I have a new Member entity and no payment. The user cannot log in yet, because they haven't activated their account, PLUS I don't want them logging in because now they have access to my website and I didn't get paid! If I take the payment first, and then inserting Member records fails, now I have $$$ but an incomplete Member. Like above, they don't have a way to log in because this time the records didn't get created. On a side note, how likely is it that any or all of this could fail? Is that a valid concern on a busy server where maybe 10 people are checking out at once, of would this be a rare event? I'm sorta leaning towards my original idea which is to create all Member records first - using a transaction - and then try and process the payment. If the INSERTS fail, then I can roll them back and never charge the person. And if the INSERTS succeed, but the payment fails, then I simply roll back the Transaction and nothing lost. (If I do that, maybe I need to remove sending the email from the transaction, because once I collect the $$$, I cannot automatically undo that using a transaction. So by pairing up the INSERT and the $$$ part, it's either all or none, but if the "Account Activation" email fails, that is easier to deal with manually.) Thoughts?

Well, my question is coming from a standpoint of not creating a nightmare for myself if things should fail - since I don't have a staff of people to clean things up. Before continuing the discussion, if it were you, would you create the records in the database and then process the payment, or would you get the $$$ first, and worry about the other things later on?

Hello. I can use some help in figure out how to implement a "transaction". I am building an e-commerce site where people buy subscriptions to online content. When a person subscribes for the first time, the following things need to happen... 1.) Create a Member record 2.) Create a TOS record 3.) Create an Order record 4.) Create an Order Details record 5.) Submit payment details to Card Processor 6.) Receive the response from the Card Processor (Hopefully a "Pass") 7.) Display an "Order Confirmation" page. 8.) Send out an "Order Confirmation" e-mail to serve as a 2nd receipt. 9.) Send out an "Account Activation" e-mail My original plan was to wrap all of those 9 steps into a "transaction", so that if any of those steps failed, then the user would get a message during checkout like, "Sorry, your checkout could not be completed. Please contact Customer Service." However, I am also wondering if maybe that is too rigid? What I don't want is a scenario where all of the records are created, but let's say the payment fails because the user is over his/her credit limit, and then I have an issue where a new "Member" has been created, but in essence I have an "orphan" because they cannot go back and try to re-register with another credit card because they are now an existing Member. Even something as seemingly innocuous, like the "Account Activation" e-mail not being sent, would create havoc. At the same time, I don't want things so rigid that it backfires. Any thoughts on this?

How do I use jQuery? I am trying to keep this as simple as possible and not have to include a bunch of libraries and frameworks. (Especially since I'm not sure how they could get added to the webserver I am using.) Would think that using pure Javascript would only be a few lines of code. Again, I have no clue of how Javascript works or how to incorporate it.

I created a "/utilities/session.php" script and require_once that script in may main scripts. Unfortunately, that didn't fix the issue. I then added into "session.php" this... if (session_status() === PHP_SESSION_NONE){ start_session(); } That made my error go away. I understand what you say about, but when I look at my "index.php", I do NOT include any other PHP scripts which contain a start_session(). (My "index.php includes a "config.php" file and that's it. SO what you are claiming CANNOT be an issue. And that page is nothing more than a login form.) Likewise, if I look at my "menu.php" script which is where you go after you login, the only script I include is "config.php" which just has constants defined in it. So while I don't deny what you are saying that calling a script with a start_session() in it multiple times will cause an error, that is not happening in my case. Creating a session.php script is a good way to consilidate code, though. But only adding the session_status() fixed my problem fwiw.

Hello. I don't know Javascript but could use some help figuring out how to do something simple. I have an unordered list <UL> and when a user clicks on a given list item <LI> I would like Javascript to perform a task for me. How would I do that?

@kicken to the rescue!! So I was right on that point. My website is super simple and only consists of 4 main pages, and then some extra things like page-not-found scripts. Could the issue be caused by my mod_rewrites? Waiting for your answer to the previous questions, but if my page is loading multiple times, then how would an include solve that issue? I am a aware of that, but I prefer solving things in code since I am not an Apache expert. So I was going to say that I think this would be the way to solve my issue... if(session_status() == PHP_SESSION_NONE){ //session has not started session_start(); } I guess you will say that while correct, I don't need that code, right?

You really like to troll for other people's code, don't you? Not interested in your help. There are others here that will answer my questions...

I have already said I do NOT have sessions in my include files...

You are proposing that I take a class on PHP. That is overkill. I am just interested right now in refactoring this particular problem without going for gold-plating! 😉

I see various comments on how to do things in the manual, but I don't know which approach is best... https://www.php.net/manual/en/function.session-status.php

Thanks, but no. I am looking to fix how I currently sue sessions, not re-architect my site.