Hi,
I really like the "insert-only" ordering model described by Johnson in
http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/b8785791f46dd091/ec1a4f6e5a38a60c?lnk=gst&q=order+schema#ec1a4f6e5a38a60c and would like your advice on how to model reserved inventory and updates that results in a refund or extra charge. Please see our "insert-only" schema below.
Summary of "insert-only" approach: The polyphasic timeline or "insert- only" approach, in which rows in the tables are only ever inserted, never deleted or updated (except for the inactivated_at column). This is accomplished by keeping "created_at" and "inactivated_at" columns on every table. The appearance and business effect of an update is accomplished by inactivating the existing row and creating a new row. The appearance and business effect of deleting a row is accomplished by inactivating the row. Rows that are currently active are readily identified by "inactivated_at is null" in the sql.
We model previous charges in a 'transaction_amount' field in the orders table. A new transaction amount represents the extra charge necessary to pay for the order
order.transaction_amount = order.price - sum_all(inactivated_order.transaction_amount)
If order.transaction_amount is '< 0' it's a refund and if it's '> 0' it's an extra charge. Please let us know if you have any better ideas.
I'd also like your advice on how to model reserved inventory. When the user initiated an order we reserve the inventory for up to 30 minutes. Inventory is currently reserved by setting a 'order.completed' flag to false, and we inactivate an order if it is not completed within 30 minutes. Another idea is to create a new table Reserved that has_one reserved Order. When the order is processed or time runs out the Order referenced by Reserved is inactivated.
Please let me know what your advice is.
Thank you in advance.
Best, Andy
ActiveRecord::Schema.define(:version => 20120213011540) do
create_table "line_items", :force => true do |t| t.string "product_id", :null => false t.string "order_id", :null => false t.integer "quantity", :null => false t.integer "price", :null => false t.datetime "created_at", :null => false t.datetime "updated_at", :null => false t.datetime "inactivated_at" end
create_table "orders", :force => true do |t| t.string "space_id", :null => false t.string "ip_address" t.boolean "accept_eula", :default => false t.string "email" t.text "address" t.string "pay_type" t.boolean "completed", :default => false, :null => false t.integer "total_price", :null => false t.datetime "created_at", :null => false t.datetime "updated_at", :null => false t.boolean "attend_event" t.text "comment" t.datetime "inactivated_at" t.integer "transaction_amount" end
create_table "products", :force => true do |t| t.string "title" t.string "description" t.integer "price", :null => false t.string "currency", :null => false t.string "event_id" t.integer "max_spots", :null => false t.integer "taken_spots", :null => false t.integer "spots_req", :null => false t.integer "max_spots_per", :null => false t.datetime "created_at", :null => false t.datetime "updated_at", :null => false end
end
