I have multiple objects with multiple states. I want to build a timeline for each object. For example: an object might have a created, submitted, processing and success state. I would like to store when the object's state changes. I have 2 different solution in mind.

  1. For each object model add columns with [status]_at name. In the above case the model will have created_at, submitted_at, processing_at, success_at columns.

  2. Store the current object state in the object model. Create a separate table which stores the object state with time. In this case even if I want to add more states to objects it will not change my db schema.

What is the best way of storing the data? What are the pros and cons of the above solutions? Open for other solutions also.

  • Do You need to store states only or You need to store their timestamps too? In the first case You can add one field with SET data type and created_at, submitted_at, processing_at, success_at values list. In the second case I think Your technique 1 is the best way. – Akina May 11 '18 at 8:18
  • Is there a well defined series of transitions in your system? – Vérace May 11 '18 at 8:21
  • @Akina I want to store both state and timestamp. If i use SET data type will it make the update difficult? – Anand Hegde May 11 '18 at 9:32
  • @Vérace yes there will be defined series of transitions. – Anand Hegde May 11 '18 at 9:32
  • @AnandHegde I want to store both state and timestamp. If so, as written above, I think Your technique 1 is the best way. The data type of fields added will be TIMESTAMP DEFAULT NULL, and NOT NULL value means this state was reached. – Akina May 11 '18 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.