Design tables and models for shipment and changes and keep it normalized

Question

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?

Answer 1

In order to have a good model, you should first not think about persistence. Persistence is another problem that should be solved separately. Thinking about the 2 problems at the same time will often result in some unnecessary complexity. So let's break down the question in two:

How to design the domain model: The domain model should reflect the business. In our case, an item has only one shipment. And Salesman has shipment history. I would suggest the following domain model. The Item class has a property shipmentSchedule that should be an instance of ShipmentSchedule class (should be a value object). The Salesman class has a shipmentHistory which is a collection of SalesmanShipmentSchedule. Each time a schedule is created by a Salesman, the item's shipment schedule is updated and a new SalesmanShipmentSchedule is added to the Salesman object (this can be done via events)

How to persist the model: I think this is pretty simple to persist. I see that for this project you are using a relational database. For the ShipmentSchedule you can have a separate table but in this case, I don't think Shipment needs an identity so it can be a value object that is serialized and stored in one column. If you are using doctrine, you can store it as embeddable or just serialize it as json (Postgres and MySql >5.7 are supporting querying json fields). For the SalesmanShipmentSchedule, you can create a separate table that contains all the shipment schedules.

I think this is a simple model for now but it can get complicated when adding more entities and value objects to it. And then you might need a good way to separate domain and persistence. Here is a nice article tackling this kind of problems