How to store paid and unpaid orders in database?
Nikunj Madhogaria
I'm designing a database where I need to store paid orders and unpaid orders of a customer. Both categories of orders have same attributes and relationships with other tables.
I have come up with two designs:
1. Separate tables for the two categories of orders:
Pros: This design helps me to quickly differentiate between the orders in cart and those which are paid. Whenever a customer revisits my website, I just need to lookup the UnpaidOrder table for that particular customer and my cart is ready.
Cons: Whenever the payment for an order in UnpaidOrder table is made, I need to move the corresponding rows (of UnpaidOrder and other tables linked with it) to PaidOrder table and its corresponding tables. Also this design will require 2x number of tables: eg. UnpaidOrderDeliveryAddress, UnpaidOrderCreditCard, ... for relationships with UnpaidOrder and PaidOrderDeliveryAddress, PaidOrderCreditCard, ... for PaidOrder.
2. Common table for both categories and an extra Status attribute:
Pros: I don't need to move multiple rows on payment of an unpaid order. Also, the number of tables for corresponding relationships is halved.
Cons: I'm storing an extra Status attribute for each row. Whenever a customer revisits my website, I need to lookup the Order table and I need to check the Status attribute (paid/unpaid) of each row for that particular customer. As a result, the cart will take a longer time to load.
My questions are:
- Which is the better design in terms of performance, keeping in mind that there are numerous users and it is a heavy application?
- Are there any good alternatives for my requirement?
Rick James
Another Opinion....
There is no need for extra normalization tables (UnpaidOrderDeliveryAddress, UnpaidOrderCreditCard, ... for relationships with UnpaidOrder and PaidOrderDeliveryAddress, PaidOrderCreditCard, ... for PaidOrder). If you argue that FOREIGN KEYs need such, I will argue against using FOREIGN KEYs in such situations. Simple indexes will suffice.
Since you will eventually have far more "paid" entries than "unpaid" entries, and paid entries are essentially 'history' that you rarely need to touch, I lean toward 2 tables.
I probably would not use a Trigger -- I would rather do it in application code where I have more control.
Are you expecting more than 100 SQL statements per second to be executed? If not, I would not call it "heavy".
Be sure to carefully surround appropriate groups of statements with BEGIN..COMMIT. Also, use FOR UPDATE on SELECTs that are leading to UPDATEs.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: the use of async in WebAPI with many async calls
- Next: CMake : External project remote URL not working
Related
Related Related
- 1
eBay GetOrders Unpaid Orders
- 2
How do I store varying length lists/orders in database?
- 3
How do I store varying length lists/orders in database?
- 4
Is this 'paid or unpaid invoices' MySQL query possible?
- 5
Send mail to users with unpaid orders php
- 6
WooCommerce: Auto complete paid orders
- 7
Android Play Store - How to offer free copies of paid app
- 8
How To get Payment By Paid Apps uploaded in Google Play Store
- 9
How to transfer Sqlite database from Free to Paid version of app?
- 10
MySQL query to display all paid and unpaid invoices where invoices can have multiple payments
- 11
How to fetch Shopify store orders using Shopify's API
- 12
Database: Design customer orders
- 13
Database Design for Orders and SubOrders
- 14
Auto process paid orders instead of auto complete in WooCommerce
- 15
Missing orders in Magento 1.8 but are showing paid in Braintree with order number
- 16
Missing orders in Magento 1.8 but are showing paid in Braintree with order number
- 17
List Customers who have paid for all the orders or have not placed order
- 18
How can I publish paid app on iPhone app store from Pakistan?
- 19
where does woocommerce store orders?
- 20
How to store a PropertyBag class into database
- 21
How to store formatted text in a database
- 22
How to store a data table in database?
- 23
how to store phonetics in mysql database
- 24
How to store user interests in database
- 25
How store order and products in a database?
- 26
How to store Font information in a database
- 27
how to store the FIX message into the database
- 28
How to store user interests in database
- 29
how to store NSString to Database as INTEGER?
Comments