Our company buys second hand valuables (item) from clients and resell them for them. In order to do the client comes to our site and ask for a shipment he may choose the time that the delivery man comes to collect the valuable. If he does not choose the time a salesman calls him and arrange the time for him. It is also possible for the client to call and ask for delivery man arrival time to be changed. Up until now we have just saved the data in shipments table like this:
id, item_id, schedule_time, scheduled_by_type, scheduled_by_id, created_at
scheduled_by is polymorphic can be Clint or Salesman
If the client would like to update the time we would have just update the shipment row. Now the company to to make a report of the scheduling created each day (for bonuses to the salesman) so I need to keep the data also for the changes. I also need to keep the possibility to get the latest schedule for a shipment. I would like to know what is the best practice to do that meaning making the data as closest to domain concepts and not duplicating data (normalizing). Performance is not a big issue.
My idea to remove the schedule_time, scheduled_by_type, scheduled_by_id from the shipment table and to create a new table called shipment_schedules. I will also added latest_shipment_schedule_id to the shipment table
So shipment_schedules will be:
id, shipment_id, schedule_time, scheduled_by_type, scheduled_by_id, created_at
and shipment will be:
id, item_id, latest_shipment_schedule_id, created_at
My dilemma is because I am not that this solution the best in describing the domain. Because it is not like the shipment really has many schedules like father has many children. The important stuff is just the latest schedule. The new table is just for history saving. Another approach would be keeping the shipment table as it was before creating the new table and maybe rename it shipment_history or shipment_changes. It may describe the domain better because you have the last result and a list of changes that got you to this result (like undo in Word) but on the other hand you will get duplicated data.
What do you think is the best approach to do that. It does not have to be one of these to solutions
We are using Ruby On Rails 4 and PostgreSQL 9.6 But an language does not really matter
Another question is from Ruby On Rails perspective. I want to update the shipment and create the shipment_schedule records with single call from the controller. I am doing that with Active Record Callbacks. should I use a after_create callback on the ShipmentSchedule Model to update the Shipment model or use the after_update on the Shipment Model to create the ShipmentSchedule? In other words what comes first creating the ShipmentSchedule or Updating the Shipment?