1

I am currently adding a feature in our software where we save a timeout in the database to initialize cronjobs which get active after that timeout.

To model this I see different ways but can't say which of them is the cleanest:

  1. Create 2 columns "timeout" and "timeout_measure", where timeout_measure is a MySQL Enum with seconds, minutes, hours, etc.
  2. Create 1 column "timeout" where the timeout is always stored in seconds

Even though I tended to the 1. way at first I saw the problem that additionally to this second column a MySQL trigger would be needed to check that if a timeout is set, the timeout_measure cannot be NULL , which would be an unnecessary performance hit as far as I am concerned, because we can implement the same functionality with only one column where we save the seconds of the timeout.

So I am now tending to the 2. way but I don't like the readability of this. Having a column like so with entries in seconds, one might not understand but only guess what this means without reading a documentation of this table.

| timeout |
| 3600    |
| 25200   |
| 86400   |

So I thought about saving the unit of measurement in the column name like timeout_in_secs. But then I am curious if this wouldn't be kind of boilerplate because if there is a documentation this additional info would not be needed in each query made and whenever the column is referenced at in the code. So whenever one wants to reference to the column of this entity in the sourcecode (we use NodeJS and Sequelize as ORM) he would need to write obj.timeoutInSecs instead of just obj.timeout.

What to do? I don't want to change the whole table structure once this feature is productive, since this could end up in a big mess. Do you think it suffices to have this documented in the database documentation, or would you still go to choose timeout_in_secs as the column name?

EDIT: I know this might be opinion based but in this case different opinions and experiences might help a lot. I am on my own in this decision and simply can't decide which solution is the best/cleanest.

  • What exactly is the reason you don't want to put timeout_in_secs? Is it a purely aesthetic point of view? – George.Palacios Mar 8 '18 at 9:56
  • Kind of, yes. I am just curious if this was redundant if in some documentation it is stated that the timeout column saves the timeout in seconds. So I am curious if others would choose "timeout_in_secs" over "timeout" as the naming of the column, because I can't remember to ever have seen a column name with the unit of measurement in it. – Vegaaaa Mar 8 '18 at 10:02
  • I have added a little explanation why I am not so happy with choosing "timeout_in_secs"as a name after the example view of the timeout column. – Vegaaaa Mar 8 '18 at 10:08
0

I would go with one unit (seconds). Then I would have the input/output convert as needed.

Controlling cron from a table?? Turn it around. Have the job continually running, but sleeping a computed length of time. If necessary, use a table for communicating the "when to run next". Be sure to watch out for edge cases -- such as being told that the next run time is yesterday. It should probably be interpreted as means "run immediately".

  • Thanks for your response. "Controlling cron from a table". No, this is not 100% true. We want our clients to be able to create cronjobs for triggering purposes during runtime. These cronjobs are created in the backend logic but saved in the database for controlling and monitoring purposes, and to possibly change mistakes made from the clients during runtime. I have implemented an own managing component which reacts to cronjob ticks, cleans the database if a cronjob ran successfully and itself checks for broken cronjobs by an own cronjob. A little too much to explain in a comment. – Vegaaaa Mar 11 '18 at 11:16
  • @Vegaaaa - Also consider EVENTs in MySQL. – Rick James Mar 11 '18 at 16:53
  • Thank you, I am aware of MySQL events. The problem is in our application domain it would have been much more complicated implementing the requirement via MySQL Events. The required triggers were coupled to our domain and could not be modeled easily using database only calculations. – Vegaaaa Mar 12 '18 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.