Jump to content



Recommended Posts

I have a column where I'm a little unsure of the best datatype to use...

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


	- 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 by SaranacLake
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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


Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


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