What is the best way to store months in a database? [closed]

Navigation
0

I am doing a contracting system and the contracts can be extended up to one (1) year, so the user will only insert the day of creation of the contract and the months of the contract.

As you can see in the image, create an enum type column for a range of values from 1 to 12 to later calculate the contract expiration dates through PHP.

I did it this way since the data type INT(2) or VARCHAR(2) could insert values outside the range [1-12]. I could also declare the data type DATE inserting under the following format 0000-03-00 (which would be "March") but as I think the database should not perform these calculations I decided for the ENUM .

Is that an adequate way to do it? How could it be done better?

    
asked by Sr. K 25.01.2018 в 18:54
source

2 answers

1

For me the best way is to work directly with dates, in the client's part I imagine that is when you get the value of "months" or rather of time of the duration of the contract. If for example, today is 01/25/2018 and the person selects 6 months, it would be 07/25/2018 . To get that you can get the current date and do something like this: 

$cantidadMeses = 6; //por ejemplo
$fechaActual = date("Y-m-d");
$finContrato = strtotime(date("Y-m-d", strtotime($fechaActual)) . " + ". $cantidadMeses ." month");

And save $finContrato in the database, with that date, make the relevant calculations.

    
answered by 25.01.2018 / 19:01
source
1

Do not complicate your life. You yourself have given the answer, when you say: so the user is only going to insert the day the contract was created and the months of duration of the contract. It follows that you will need two data:

  • beginning of the contract
  • end of contract

Even if you only need to calculate the months, it does not matter, use two fields DATE or DATETIME ! and from there calculate what you want. The data will be easier to insert and maintain.

For example, for the insertion, if you want, in the graphical interface you can ask for a start date and a whole number of contract length and from these two data you can calculate and save in the database the end date .

And also, who tells you that the data model will not evolve later and they will ask you for precise dates? How are you going to capture them if you have several thousand or millions of records with that information stored in whole numbers?

Save the data as what they really are: if they are dates, save dates and from your data calculate what you have to calculate.

Do not try to save information in your database that is part of the logic of the program (which may vary). The databases are for saving data, based on reality.

    
answered by 26.01.2018 в 01:56
Why do the MAX () and MIN () functions give me the same result in Mysql? Do not load the values in input