SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recurring Orders Table


Recurring Orders Table

Author
Message
anthony.obrien-797674
anthony.obrien-797674
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?
WI-DBA
WI-DBA
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 605
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search