TransWikia.com

Data modelling for specific units of measurement

Database Administrators Asked by Vegaaaa on December 12, 2021

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.

One Answer

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

Answered by Rick James on December 12, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP