What to use to store status in a database?

Question

I am creating a database in Mysql and in it there will be a table to do point control: when the employee started working hours, when closed, when stopped for lunch and etc.

I’ve been making a prototype table:

+------------------+---------------------+--------+----------+-------------+
| employee_name    | datetime_registered | status | register | observation |
+------------------+---------------------+--------+----------+-------------+
| Fulando da Silva | 2020-10-28 14:49:00 | 0      |        1 | NULL        |
+------------------+---------------------+--------+----------+-------------+

Whereas status is a code that will translate to: start of hours, end of hours and etc. And the backend would have a business rule to make this translation from code to value. I thought this way for memory saving, since storing a string of type 'Start of business' takes more long-term memory than just '0', '1' or '2'.

But I don’t think this is the best practice for it. I researched about it and found nothing directly related to it.

Explaining the rest of the table: the field employee_name keeps the name of the official, the field datetime_registered guard the time the office status has been updated, register is the id of each record, being the primary key of the table, and observation is a text field that stores some occasional justification, if the employee closes early and needs to justify briefly.

Answer 1

It seems obvious to me that the guy tinyint is best suited to store the status codes and then in the application makes the description on top of each code. But any integer can be useful if space is not absurdly restricted. I have already talked about this in Varchar or int for "type fields".

Some people may think of a enum but I see no advantage. It has link in response linked who talks about the disadvantages of this.

And of course you can have an auxiliary table with the description of the states, which can bring some advantage, but I think it brings too many disadvantages to adopt. Every query would have to do a secondary search which would slow down. You would have to use a foreign key. In some scenarios may not be a problem, but I think it complicates the writing, leaves it slow to have a very small ease in reading that the disadvantage ends up undoing.

I can’t think of a strong reason to use string in this case because it is an identifier code, it is not something descriptive, but also it is not a big problem, even consuming a little more space, only find easier the number.

I don’t know the whole problem, but can that the modeling is bad and should do another solution that may not even involve this column. It might be ideal anyway, I’m just opening the door to think about.

Answer 2

I thought that way for memory saving, since storing one 'Start Time' type string occupies more long-term memory than only '0', '1' or '2'.

But I don’t think that’s the best practice for it. I researched about and found nothing directly related to this.

First, the status can be described in the database normally, the problem here is that it is denormalizing your table.
If the purpose of the table is to store the point data, and the status is not part of the key, according to the normalization should be in a different entity, for example "Registerstatus".
This table can have the id and status description, there must be a single record by description, and the space consumed is irrelevant based on what you put as an example, and the table you put in the example, only the id, which would be the foreign key. Either:

That is, a table/entity for the status, which solves the description problem, and a Foreign key in the other table, solving the field type problem and also the correct normalization of the model.