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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy