Recurring Orders

Hi everyone I'm working on a school project, and for my project I chose to create an ecommerce system that can process recurring orders. This is for my final project, I'll be graduating in May with an associates in computer science.

Keep in mind this is no where a final solution and it's basically a jumping off point for this database design.

A little background on the business processes.

- Customer will order a product, and will specify during checkout whether it is a one time order or a weekly/monthly order.

- Customer will specify a location in which to pick up their order (this location is specific only to the order)

- If the value of the order > 25.00 then it is accepted otherwise it is rejected.

- This will populate the orders_test and order_products_test tables respectively

• Person on the back end will have a report generated for deliveries for the day based on these two tables.
• They will be able to print it off and it will generate a list of what items go to what location. Based on the following criteria.
• date_of_next_scheduled_delivery = current date
• remaining_deliveries > 0
• Once they are satisfied with the delivery list they will press "开发者_JAVA技巧Process Deliveries" button.
• This will adjust the order_products_test table as follows
• Subtract 1 from remaining_deliveries
• Insert current date into date_of_last_delivery_processed
• Based on delivery_frequency (i.e. once, weekly, monthly) it will change the date_of_next_scheduled_delivery
• status values in the order_products_test table can either be active, hold, or canceled, expired

I just would like some opinions if I am approaching this correctly or if I should scratch this approach and start over again.

A few thoughts, though not necessarily complete (there's a lot to your question, but hopefully these points help):

• I don't think you need to keep track of remaining deliveries. You only have 2 options - a one time order, or a recurring order. In both cases, there's no sense in calculating remaining deliveries. It's never leveraged.

• In terms of tracking the next delivery date, you can just keep track of the day of the order. If it's recurring -- monthly or weekly, regardless -- everything is calculable from that first date. Most DB systems (MySQL, SQL Server, Oracle, etc) support more than enough date computation flexibility so that you can calculate this on the fly, as opposed to maintaining such a known schedule.

• If the delivery location is only specific to the order, I see no use in creating a separate table for it -- it's functionally dependent on the order, you should keep it in the same table as the order. For most e-commerce systems, this is not the case because they tend to associate a list of delivery locations with accounts, which they prompt you about when you order more than once (e.g., Amazon).

• Given the above, I bet you can just get away with 2 of your 4 tables above -- Account and Order. But again, if delivery locations are associated with Accounts, I would indeed break that out. (but your question above doesn't suggest that)

• Do not name your tables with a "_test" suffix -- it's confusing.