Good day everyone. I spent a whole day looking for an answer to my question but unfortunately I did not the answer I'm looking for.
I am trying to automate some processes that we are currently using by creating a web application in PHP and a MySQL database.
I would like to prepare the staff schedule in excel then upload it to MySQL using php. Most of the database fields are straight forward: an employee ID that is INT, date that is in DATE format.
The issue I am facing is in the time. When an employee is working, I would like to upload their shift start time and end time in TIME, however, if they are not working, I would like the database to store their status in letters: OFF for a day off, AL for annual leave, ML for medical leave, EL for emergency leave and so on.
I do not know what data type will support this. ie: accept either time or an entry out of a specific list of outcome codes.
Any assistance will be appreciated.
Mohamed.
Side Note: I asked this question in a time where I was beginning to learn how to code, one of the mistakes that I have learned to avoid is mixing business logic and application logic. This question may be useful to someone in the future, and my advise would be to any new developer is to make sure that the business model logic may not go hand-in-hand with your application logic, but there is always a way to make it work.
Mohamed.
Here is an idea, put another column for status, one of the status will be working status and every other status will be as described. Only working status will have data in the "start time" and "end time" columns. In my opinion this is the best solution and allows for better search capabilities, cleaner database and more comprehensive readability.
However, if you absolutely want to, and/or have any reason on why you can't have an additional column, you can always store your time as text.
PS: Another tip for your database is to drop the date column and store both times in DATETIME format, it may range from unlikely to nearly impossible depending on what job shifts you are storing in the database, but it is possible to start a shift on one day and end it the next day, and even if you think you won't ever need it, it is good practice and makes the database more resilient. If you had to change it in the future it would be a pain to do so.