Orders Schema

Per the mailing list, the following schema design is planned to accommodate the order system. (Also see the notes on order workflows.)

orders Table

column	type	notes
uuid	String(32)	primary key
customer_uuid	String(32)	relates to Customer; may be None
created	DateTime
creator_uuid	String(32)	relates to User (i.e. not Employee)

order_events Table

column	type	notes
uuid	String(32)	primary key
order_uuid	String(32)	foreign key to Order
timestamp	DateTime
type	Integer	enumeration value; workflow-dependent
user_uuid	String(32)	relates to User
notes	String(255)

order_products Table

Note the two discount fields below. It's assumed that only one of these will be provided by the user when the order is being created, and the other one will be calculated based on the price. More info is in the workflow.

column	type	notes
order_uuid	String(32)	composite primary key
index	Integer	composite primary key
product_uuid	String(32)	relates to Product; may be None
product_description	String(25)	cached copy of Product.description, assigned when Product is resolved
quantity	Numeric(8,3)	quantity being ordered, decimal allowed
price_uuid	String(32)	relates to Price
discount_percent	Numeric(6,2)	percentage discount to be applied
discount_amount	Numeric(8,2)	dollar discount to be applied
extended_price	Numeric(8,2)	cached "full" price (Price.amount * quantity - discount_amount)
status	Integer	enumeration value; workflow-dependent

order_product_descriptions Table

Note that this table is provided only for situations where an OrderProduct.product cannot be resolved at the time of Order creation. The description field will actually be mapped as OrderProduct.initial_description; see the workflow for more info.

column	type	notes
order_uuid	String(32)	composite primary key
index	Integer	composite primary key
description	Text

order_product_events Table

column	type	notes
uuid	String(32)	primary key
order_uuid	String(32)	foreign key to OrderProduct
index	Integer	foreign key to OrderProduct
timestamp	DateTime
type	Integer	enumeration value; workflow-dependent
user_uuid	String(32)	relates to User
notes	String(255)