SaranacLake

@kicken, Hello. Could you please respond to my comments and questions in the post above? https://forums.phpfreaks.com/topic/310266-handling-subscription-dates/?do=findComment&comment=1575362

Forgot my brain tonight! 😀 Since this is a WEBSiTE, I guess everything the user does is ultimately on my server and thus using my local time. (I guess if I had something like a receipt or show the details in the user's profile I could "localize" the time, but that probably isn't such a big deal as long as I display the time zone (e.g. EST). So I guess this would be a non-issue. I'm not looking to split hairs, so I would round up until right before Midnight (i.e. 23:59:59). But what do you think about using just DATES versus DATE&TIME? Is there any downside to using TIME also? I guess I was just afraid that it would complicate things. For example, to use your example, if you subscribed on 2020-04-17 @ 5:20pm, I guess I would insert thate xact time as the "start_date" but then how would I easily insert an "end_date" of 2020-04-17 @ 23:59:59? Maybe it is as easy as grabbing the DATE from the TIMESTAMP and then using my PHP code to append on a "23:59:59" to the DATE? Or is there a PHP or MySQL function that would do this for me? Any other thoughts on how to best handle all of this?

No, I do not. I think users will think, "Oh, hey, my subscription starts/ends on April 17..." So if I just use Dates - per #1 - then while 2019-04-17 to 2020-04-17 would technically be one year and one day, I think most people would see it as one year. And excluding nit-picking, you could argue that implicitly going from April-7 to April-17 handles the Time too. But would it break anything in my database if I have records like... +----+-----------+---------+------------+------------+ | id | member_id | plan_id | start_date | end_date | +----+-----------+---------+------------+------------+ | 1 | 25 | 3 | 2019-04-17 | 2020-04-17 | | 2 | 25 | 3 | 2020-04-17 | 2021-04-17 | | 3 | 25 | 3 | 2021-04-17 | 2022-04-17 | +----+-----------+---------+------------+------------+ Well, like I said, if a user subscribed in the future relative to my server (e.g. 2020-04-17 4:00am UTC) and the record was stored in my database showing my time (e.g. 2020-04-16 11:00pm EST), then my PHP code wouldn't let them log in and get "premium access" until it was April 17 n my server, thus forcing the customer to wait 5 hours for the U.S. time to catch up with Europe?! Right?

I could use some advice on how to best handle subscription dates in MySQL. This may seem like an easy thing, but working with dates and time and changes across days and years and timezones can actually be rather complex! Let's say my table looks like this... SUBSCRIPTION - id - member_id - plan_id - start_date - end_date And let's assume that subscription are for 1 year. If a user subscribes on 2020-04-17 @ 3:00pm local time, then how should I handle the "end_date"? 1.) Would it be easier to just work with Dates and avoid Time? 2.) If so, does a Subscription have a start_date of "2020-04-17" and an end_date of "2020-04-16" or an end_date of "2020-04-17"? 3.) How do I handle - I think it's called "localization" - where the client time and server time are different? For example, if a user is in London and subscribes on 2020-04-17 @ 4:00am UTC but my server is in the U.S. and has a time of 2020-04-016 @ 11:00pm, do I make the user wait an hour before their subscription kicks in because my database is on Eastern Time? Typically I am for "precision", but I also don't ant to make dealing with Dates and Times so difficult that it creates a real hassle for me from a design and coding standpoint. Oh, I guess my questions apply to both PHP and MySQL! 🙂

Looks like this is a smarter way to go... Survey App

Interesting, but probably not a scalable solution (e.g. surveying hundreds of people). Is it even possible to run a webserver like MAMP on an iOS device? If I don't have reliable Internet, then what are my other options?

What would those be? If I have Internet access I could utilize it, but I am assuming that if i was walking door to door or in a building or out in the country relying on working Wi-Fi/Internet to be able to take surveys would be risky at best. So I guess I would like to build something *IF* I can avoid it taking me 6 months.

I guess I was assuming that I wouldn't have Internet connectivity. That's an interesting angle that I never thought of... if I had Internet access, then I could just build a website. Let's assume that I do not have Internet access... So what would be the easiest way to do what I want, which to be clear, is a survey form that I could complete while standing somewhere talking to Mrs. Jones, click "Submit" and have the survey results written to a local database. Then when I got how, I could at the very lest export the results into a spreadsheet and be able to see what people said. I'm pretty sure that things like Angular are designed for such self-contained applications, but I fear that it would take me forever to learn Javascript and Angular plus things like OOP.

So what are you suggesting?

Hello. I would like to build a survey that I can use on an iPad so that I can go out in the field and gather data from people. What would be the best way to accomplish this? I'm not sure what limitations even exist on an iPad - like can you have a webserver like MAMP? It seems to me that using something like Angular or whatever might make sense since you can build a working app all in one, but then I don't even know traditional Javascript. Just looking for a quick way to build something to allow me to survey people without making this a major project.\ Thanks.

Thanks for the thoughts and feedback, @requinix and @gizmola Trying my best to learn things the correct way so that I don't cause bigger issues down the road! This is good information to know!

@gizmola, What if I had said... 1.) "A Primary Key is a key that also HAS A constraint and HAS AN index built into it automatically by MySQL." 2.) "A Foreign Key is a key that also HAS A constraint and HAS AN index built into it automatically by MySQL." 3.) "A Unique Key is a key that also HAS A constraint and HAS AN index built into it automatically by MySQL." Also... 4.) "A Key is a KEY, but if it is a Primary/Foreign/Unique Key, then it also comes with a CONSTRAINT and an INDEX." 5.) "A Key is NOT an "Index", but it comes with one built in for performance reasons." 6.) "An Index can exist on a column WITHOUT having an associated Constraint or Key." Agree or disagree?

Based on my research last night, I would say that... A Primary Key is a CONSTRAINT. (But it also has an Index built into it automatically.) A Foreign Key is a CONSTRAINT. (But it also has an Index which you can define with the same name as the Constraint or the Index can have its own name.) A Unique Key is a CONSTRAINT. (But it also includes an Index built into it - at least with MySQL.) In MySQL 5.7, INDEX types include: Primary, Index, Unique, Spatial, Fulltext A straight up "Index" is a pure INDEX with no Constraint. (As mentioned above, Primary Key, Foreign Key and Unique Key/Index are CONSTRAINTS that also have INDEXES - a "combo deal". 🙂 I think in Oracle the Primary Key and Foreign Key forces you to set up the CONSTRINT and INDEX separately, but I'm not entirely sure. In summary, people use these terms pretty loosely - especially with MySQL - but there are clear differences as to what each things is and does. CONSTRAINTS constrain/restrict/limit... INDEXES speed up queries with multiple JOINs, as well as speed up searching within a table.

Okay, maybe that is so in MySQL, but in general database terms a "key" and and "index" are NOT the same thing! I had a chance to read some more on this topic, and just ponder things, and I think MySQL and people in general use these terms loosely without fully comprehending them...

Yeah, I thought about that afterwards and agree that there is no need for it. It also hurts readability! Yes, this is the format I follow now. Yeah. True. I have been Googling different naming conventions and hope to settle on something tonight. There are lots of interesting ideas with pros/cons to each. I strive to make everything I do be self-documenting, but this is one area I forgot about until I started creating my test database for my entitlements, and I realized this was a topic that needed a deeper dive to do it right!

Sorry for all of the questions, but as I was reading up on naming conventions for Indexes and Constraints, I have come across lots of other material that leads to even more questions! What exactly is the difference between a database (generic) "KEY" and a database "INDEX"? This is my understanding... A "KEY" is how you join tables together. So, you might have a "PRIMARY KEY" (unique) for the parent entity, and then a "FOREIGN KEY" (not unique) for the child entity which ties back to the parent entity. "KEYS" are all about linking tables. You also have "UNIQUE KEYS" (unique but allow Nulls). An "INDEX" is designed to speed up queries. It does not have anything to do with table joins. Where it gets confusing is that there seems to be some overlap. For instance, a "PRIMARY KEY" also has an "UNIQUE INDEX" built into it. And when you create a "FOREIGN KEY (Constraint) in MySQL, you are also creating a "(Foreign key) INDEX). And I understanding the concepts of a "KEY" and an "INDEX" correctly?? P.S. I believe that a "Primary Key" and a "Foreign Key" are more correctly called "CONSTRAINTS" as in a "Primary Key Constraint" and a "Foreign Key Constraint", right?

Well, it is too late for me to prefix the Table alias onto my Column names, but maybe I can do that for v2.? Which one could argue is a reason to let MySQL do it for you. Or to my point, it is a reason to come up with a good naming convention! Interesting, I didn't know that!

I'm reading a blog that suggests using table aliases in your constant names. For example, if you have... order AS ord order_details AS ordd Then your FK constraint would be.... fk_ord_ordd This article also suggests using a Table Alias to pre-pend your Column Names. (Interesting idea, although that would require a lot of thought up front.)

I see you're not in a serious mood today....

Do you name your Constraints? And if so, what naming convention do you use? I suppose I can see where naming Indexes maybe isn't as useful, but if you have a bunch of Constraints (e.g. Foreign Key, Check, etc) then I would think you would want to keep them organized?!

Is there a need to know an indexes name so that you can refer to it, or is it a MySQL internal that no one really cares about?

So it doesn't sound like you find value in naming your indexes? (I take the first comment to be sarcasm...)

A-ha. Yeah, I was thinking that is what you'd say. No, I considered that, but in my ERD that wouldn't work because I have a fork like this... >|-------||- MY_TABLE -||-------< (I am brushing up on MySQL so I can create a "test" database and try out my hypotheses!)

Do you name your Indexes in MySQL? If so, what naming convention do you use?

New lone being what? A carriage return or something like /n /r or <br>