May 20, 2009 at 5:20 pm
I'm having some trouble in deciding what table structure to use for a milk delivery app. Customers have the same (mostly!) orders/delivers every week, and deliveries are made every day (though not nesessarilly to every customer).
I was thinking of using an orders table and orderdetails table, but the application will also have to handle deviations from the default orders. So will I need another orders table or else if a deviation is made from a daily order, it is replaced with a once off daily order for that day only?
Order Table:
OrderID (PK)
CustomerID (FK)
IsRecurring
DayOfWeek
OrderDetail Table:
OrderDetailID (PK)
OrderID (FK)
ProductID (FK)
Quantity
UnitPrice
There will then be a delivery table to record what was actually delivered and on what date, which will be used for billing purposes.
Delivery Table:
DeliveryID (PK)
CustomerID (FK)
ProductID (FK)
Date (FK)
Has anyone any opinions on a more efficient way to handle this problem?
May 26, 2009 at 8:41 am
I would keep your order structure and probably create a SCHEDULED_ORDER table and have a job add the order to the table or the application pull in the defaults and allow for changes. This way you are separating an actual order from a possible one.
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply