It's an internet stop, physical products with delivery.
I'm trying to figure out how to implement transactions/payments, fulfilment, shipping, payments, refunds, etc.
Namely, a status of each sale.
Approach #1:
create a column "status" in "sales". It could the following values:
new,
payment_pending,
paid,
fulfilment_started,
fulfilled,
shipping_started,
shipping_in_progress,
delivered_successfully
It may also contain:
refund_claimed,
refund_in_progress,
refunded.
This isn't the complete list of statuses
Approach #2:
create tables for each process:
payment_logs (id, sales_id, status, details)
shipping_logs (id, sales_id, name, status, details)
refund_logs (id, sales_id, status, details)
The approach #2 is more flexible. Yet, there's a downside: how would I know the current global status of a sale?
Is it being delivered?
Or Is it still being fulfilled?
Or is a payment pending for it still? That is, it's not even proceeded to fulfilment, nor delivery procedures.
To solve that, I'd have to introduce logic to this approach: search in "payment_logs".
Found, successful? -> proceed to "fulfilment_logs". Found, but not yet "successfully_fulfilled"? Then the global status of a "sale" will be whatever the current status in "fulfilment_logs" is.
Will this, the approach #2, work nicely? Are there any other downsides?
What would you propose?
Best Answer
I had a document approval and publishing module that tracked reports through their status from writing, approval, revisions, and finally publish. The way we tracked it was to have a table with the document id, current status, and a timestamp. Any time the status would change it would update that document's record with the datetime it happened. The only thing to be aware of was that we did not store historical information, so when a report was published we couldn't look back to see when it was originally written and approved by the supervisor.