Tag: in English

I’ve just had another very useful lesson on what to not do. In one of my school project I have one table in database that represents orders and another that keeps events associated with orders. The latter one simply has an foreign key to order table. Each event has timestamp and a number that references order state after the event. State is stored as a number and it references third table which keeps all numbers with their respective states. To find out which is the current state of an order you simply run a query that selects all events associated with that order and finds out which one is the newest.
Turns out, that’s exactly how not to design a database! While we were showing off our work to our lecturer the strangest thing happened, after he confirmed an order it stayed in the same state as before! Very bad considering that every time an order is confirmed the number of products in warehouse is increased by the amount in the order by application. Confirming the same order more than once is something that should not be possible! And our application did have a check whether the order in a state that allows the next state to be “confirmed”.
My first suspect was the type of event that is inserted would be wrong. We had one such case with the same project earlier. Stupid, but yes, that’s exactly why you need to test everything. That was not the case, quick look on tables showed that this part was correct.
Next was the probability that the database view that displayed each order’s state was incorrect. That was a rather complicated query compared to simple select clause and I didn’t really feel like debugging it. Instead I took another look at the events table. This time I paid attention to the timestamp attribute as well. Imagine my surprise when I saw that the timestamp for the last event was 40 minutes earlier than the event that I know had happened earlier! Apparently while the lecturer was clicking around, confirming orders and such, our lovely server decided to go back in time by 40 minutes!
I wouldn’t have come to that conclusion unless the time wouldn’t be incorrect at the moment.
Simple script echoing time showed 18:05:59 and three seconds later 18:05:02! It was actually about 18:50.
My lesson from this: if you want to keep events like that then keep current state number in the orders’ table as well for each order. Every time when you insert new event update it. This way you get strange result when you print out all events of a certain order but you never find yourself in the position that current state is wrong.