I need guidance from all the experts here to find out the best implementation to perform following scenario :
Let's say I have a patient table where every new patient is inserted as a new row. My task is that i want to updated Status column as checkout patient after 12 hours his recorded is entered in MySQL database.
Table attribute are as follows :
Name | Age | Gender | Address | DOB | Time (When row entered) | Status|
I am using Status as
N : New Patient
C : Checkout Patient
I need to change status field from N to C every 12 hours form the difference time when its status was N.
That sounds easily doable via a SQL statement triggered in a variety of ways, depending on the circumstances. What ideas have you had? Or, to put it another way, what specific issue has you stumped at the moment? Creating the SQL statement to do so? Triggering it? Something else?
Ulf Dittmer wrote:That sounds easily doable via a SQL statement triggered in a variety of ways, depending on the circumstances. What ideas have you had? Or, to put it another way, what specific issue has you stumped at the moment? Creating the SQL statement to do so? Triggering it? Something else?
If it could be done through Triggers, that's good. I want to triggering it but don't know how ? According to my limited knowledge of triggers can shoot on INSERT,UPDATE,DELETE etc. I have time constraint on my attribute how does a trigger know about the time completion of every row ? . Correct me if i am wrong.
posted 5 years ago
I meant "trigger it" in the general sense - maybe a cron job that runs every N minutes. SQL triggers fire if something gets inserted, updated or deleted from a table - so there's no guarantee about when they run. If you want predictability (maybe the value should get updated no later than 5 minutes after the 12 hour mark), then cron jobs can provide that, whereas SQL triggers can't.
Another option might be not to store the status in the table at all, but just recalculate it - based on current time - when needed. This might even be done in a select statement, or perhaps a view could be created that would hide the logic.
Thanks Martin Vajsar for the explanation. My Question is who is going to trigger this Select view ? and what you think about this View performance after an year when table containing thousand patients entries per day? The purpose of adding status column in database is to add Where clause in query. So that patient that are with the status N fetched quickly.
The PatientView view would be used by your application instead of the PatientTable.
Good point about querying the table. That query would indeed be inefficient, you'd have to use another one:and you'd index the time column.
There isn't one best solution. The solution I describe has the advantage that there aren't derived data stored in the table, you don't need a job to update those data and the data cannot become stale because of a failed job. There are disadvantages too - you need to calculate the Status column every time you need it and cannot easily query it. It's up to you to weight these pros and cons and choose the solution. I just wanted to mention it, as my personal preference is to avoid storing derived data in the database if it can be easily avoided.