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
–
Andy Saebjoernsen
Founder & CTO
www.tableslice.com
tel: 510.332.4877
Follow us @Tableslice
Like us on Facebook