Jump to content

SaranacLake

Members
 View Profile  See their activity

  • Posts

    648

  • Joined

  • Last visited

 Content Type 

Posts posted by SaranacLake

    Handling Subscription Dates

    in MySQL Help

    Posted

    3 minutes ago, kicken said:

    You should only be using one timezone in all your code and storage.  UTC works well but pick whatever you want and use it. 

    By using a single timezone and datetime types then most of this problem goes away.  Regardless of what the users local timezone is, you store the date and time in your chosen timezone.  Then whenever you want to compare to it you also compare the current time in your chosen timezone to that stored date and time.  If you want users to view dates and times in their timezone then convert them from your chosen timezone to their timezone just prior to display.

    If your using generated times then things are pretty easy in general.  If your taking user-submitted dates and times, then things can potentially get a little more complex.  If you want users to deal with things according to their local time you'll need to convert their input from their timezone to yours before doing and processing.

    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.

     

     

    3 minutes ago, kicken said:

    This is just something you need to decide as one of your business rules.  You also need to decide if whatever date you pick is inclusive or exclusive.  If I were to subscribe on 2020-04-17 @ 5:20, for one year then does my subscription run 2020-04-17 @ 5:20 -> 2021-04-17 @ 5:20?  2020-04-17 @ 0:00 -> 2021-04-17 0:00?  2020-04-17 @ 0:00 -> 2021-04-17 @ 23:59:59?  Up to you to decide.

    If it were me, I'd probably go for for the last option as I think it's the most intuitive and what people would expect.   People are not going to remember the exact minute they signed up, and on the scale of a year I wouldn't nit-pick a few hours.

    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?

     

    Handling Subscription Dates

    in MySQL Help

    Posted

    11 minutes ago, requinix said:

    Unless you want to penny-pinch your users out of individual hours, dates are easier.

    No, I do not.

     

    11 minutes ago, requinix said:

    Which one makes more sense to you? Decide now. Don't ask. Decide.

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

     

     

     

    11 minutes ago, requinix said:

    Here's a question: does it actually matter? Think about it.

    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?

     

    Handling Subscription Dates

    in MySQL Help

    Posted

    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!  🙂

     

     

    Build a survey

    in PHP Coding Help

    Posted

    5 minutes ago, requinix said:

    What they're getting at is, do you want a survey tool or do you want to make a survey tool? Because if you have internet access then there's plenty of free options you can use without needing to create something new.

    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.

    Build a survey

    in PHP Coding Help

    Posted

    10 minutes ago, ginerjm said:

    I think the question we need to ask is - will you have internet access 'in the field' or not?  Using MAMP would imply (?) that you are completely self-contained on the iPad and not relying on a connection.

    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.

     

    Build a survey

    in PHP Coding Help

    Posted

    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.

    Key vs. Index

    in MySQL Help

    Posted

    @gizmola,

     

    On 2/26/2020 at 6:14 PM, gizmola said:

    A "Key" is not a constraint.

    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?
     

    Key vs. Index

    in MySQL Help

    Posted

    4 hours ago, requinix said:

    On the common parlance side of things, typically "index" emphases that it's related to searching while "key" refers to it being an identifier for a record. Like, if your table has a user ID key then it should also have a user ID index.

    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.

     

     

     

     

     

    Key vs. Index

    in MySQL Help

    Posted

    13 hours ago, Barand said:

    Below is an extract from the mysql reference manual

    image.png.9b0510825fcccb7f4e2606a24a0dbde0.png

    As you can see, with the exceptions of "PRIMARY KEY" and "FOREIGN KEY" (which can be considered to be reserved phrases) the terms "index" and "key" are interchangeable.

     

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

     

     

    Naming Constraints

    in MySQL Help

    Posted

    17 hours ago, kicken said:

    Don't do that.  Not in the actual table at least.  Some people recommend this stupidity to try and avoid name collisions in their queries (such as two tables have a Label column) but such issues can be easily handled using the table.column syntax in your query rather than cluttering up column names in the table. 

    Yeah, I thought about that afterwards and agree that there is no need for it.  It also hurts readability!

     

    17 hours ago, kicken said: 
    
SELECT o.Label as o_label, s.Label as s_label
FROM order o
INNER JOIN status s ON s.Id=o.Status

    Yes, this is the format I follow now.

     

    17 hours ago, kicken said:

    One of the applications I work on was original designed using a scheme like that where every column has a table specific prefix to it and it's super annoying (long names, broken autocomplete) for no real benefit. I've been slowly undoing that when I can and just giving the columns nice simple names. 

    Yeah.

     

    17 hours ago, kicken said:

    I'd also suggest just using the full table name in your constraint names rather than some alias.  It makes things very clear when someone 6 months later needs to decipher things.

    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!

     

     

    Key vs. Index

    in MySQL Help

    Posted · Edited by SaranacLake

    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?

     

     

     

     

     

     

    Naming Constraints

    in MySQL Help

    Posted

    1 minute ago, Barand said:

    That would the meet the requirement for "logical, meaningful and unique".

    Well, it is too late for me to prefix the Table alias onto my Column names, but maybe I can do that for v2.?

     

    1 minute ago, Barand said:

    Beware name clashes though. They (constraint names) need to be unique within the scope of the  database schema.

    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!

     

     

    1 minute ago, Barand said:

    Table alias name scope is only the query.

    Interesting, I didn't know that!

     

    Naming Constraints

    in MySQL Help

    Posted

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

     

     

    Naming Constraints

    in MySQL Help

    Posted

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

     

     

    Naming Indexes

    in MySQL Help

    Posted

    6 minutes ago, Barand said:

    To me it sounds like I don't name them because they will be given reasonable names automatically

    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?

    Naming Indexes

    in MySQL Help

    Posted

    2 minutes ago, Barand said:

    I name mine after Harry Potter characters.

    However if I create an index without a name using MySQL Workbench it auto names it for me. If I index column yyy in table xxx it gives it the name "idx_xxx_yyy"

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

     

    Do I need a Subquery?

    in MySQL Help

    Posted

    3 minutes ago, kicken said:

    Maybe just adding another join to your "complicated" query that connects the articles table.

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

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.