SaranacLake Posted September 18, 2019 Share Posted September 18, 2019 (edited) I have a column where I'm a little unsure of the best datatype to use... MEMBERSHIP_PLAN_PROMOTION - id - promo_code - contract_term - unit_price and so on... As I'm sketching things out on paper, for "contract_term" I was going to store values like: "1 month", "3 months", "1 year", and so on... Why? Because it is easier to read and instantly makes sense. However, in the table below, I determine the "contract_end_date" by taking the moment someone subscribes, placing that in "contract_start_date" and then using "contract_term" to calculate the "contract_end_date"... SITE_SUBSCRIPTION - id - member_id - membership_plan_promotion - contract_price - contract start_date - contract_end I had considered changing "contract_term" to an integer representing days, but then that creates other problems... - Days are harder to read, especially if I had a 2 or 3 year contract_term. (Who wants to read 730 days for 2 years, and what about Leap Years?) - Also, Days don't give you an even "offset". For example, I can more easily have my PHP read a "start_date" = #9/17/2019# and then use a "contract_term" = "1 month" and come up with a perfect offset of "end_date" = #10/17/2019# versus adding 30 days to #9/17/2019# and then - depending on the time of the year - possibly being off a day or so. Follow me? So how can I have a column that is easy to read, and yet also makes calculations easy as well? Most likely, I will just have a few choices (e.g. 1 month, 3 months, 1 year) but obviously I want a design that can grow as business needs change. Edited September 18, 2019 by SaranacLake Quote Link to comment https://forums.phpfreaks.com/topic/309247-contract_term/ Share on other sites More sharing options...
requinix Posted September 18, 2019 Share Posted September 18, 2019 If I had MySQL on hand to test with then I would test whether the DATE format allows values like "0000-00-120" (120 days). If not then you can just use a regular text type. Combined with DATE_ADD or +INTERVAL you should be able to add that interval to your date values inside queries. That's if you need to do the date math in SQL. If it was just PHP then I'd still test the DATE format for its convenience, but otherwise you can use a standard interval format like "P120D" and PHP's date functions. Quote Link to comment https://forums.phpfreaks.com/topic/309247-contract_term/#findComment-1569801 Share on other sites More sharing options...
Barand Posted September 18, 2019 Share Posted September 18, 2019 4 hours ago, SaranacLake said: Who wants to read 730 days for 2 years The number you store in the database does not have to be the number you display to the user EG <option value="730"> 2 Years </option> As your options all appear to be a number of whole months (1, 3, 6, 12, 24) then I'd store the period as integer months. <option value="24"> 2 Years </option> You can then get the end date in SQL with SELECT start_date + INTERVAL contract_term MONTH - INTERVAL 1 DAY as end_date Quote Link to comment https://forums.phpfreaks.com/topic/309247-contract_term/#findComment-1569803 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.