Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recurring Orders Table Expand / Collapse
Author
Message
Posted Wednesday, May 20, 2009 5:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 11:41 PM
Points: 1, Visits: 31
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?
Post #721033
Posted Tuesday, May 26, 2009 8:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 9:02 AM
Points: 94, Visits: 573
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/
Post #723301
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse