Code 4 Jay: Sample Table Structure for Shopping Cart Related Tables

After knowing the flow of the shopping cart, you also need to understand the back-end of the module in terms of table design and SQL insertion flow which running in the code. First and foremost, below are the sample table design:

Shopping:

• shoppingID - Every shopping cart should have an ID so that it is unique to all user.
  
• memberID - Acquire from Member table.
  
• productID - Acquire from Product table.
  
• productType - Product info, you may have something else.
• productBrand - Product info, you may have something else.
• productSeries - Product info, you may have something else.
• productPrice - Product price, is a compulsory information for shopping cart.
• shoppingQuantity - Purchase quantity.
• shoppingTotal - Total price. productPrice multiply with shoppingQuantity.
• shoppingImage - To display the product image, optional.

Sales:

• salesID - Every sales should have an ID so that it is unique to all other sales.
  
• shoppingID - Acquire from Shopping table.
• memberID - Acquire from Member table.
  
• productID - Acquire from Product table.
  
• productType - Product info, you may have something else.
• productBrand - Product info, you may have something else.
• productSeries - Product info, you may have something else.
• productPrice - Product price, is a compulsory information for shopping cart.
• shoppingQuantity - Purchase quantity.
• shoppingTotal - Total price. productPrice multiply with shoppingQuantity.
• paymentID - Acquire from Payment table, every successful sales should involved a payment.
• paymentDate - Acquire from Payment table, which mark the date of payment made.

Payment:

• paymentID - Use to identify every payment made by the user.
• memberID - Acquire from Member table.
• paymentTotal - Payment information, compulsory
• paymentMode - Payment mode: Credit Card, Direct Bank Debit, Charge Card.
• paymentSalutation - Payor salutation.
• paymentFName - Payor first name.
• paymentLName - Payor last name.
• paymentAddress - Payor given address.
• paymentCity - Payor given city in the address.
• paymentState - Payor given state in the address.
• paymentCountry - Payor given country in the address.
• paymentPhoneNo - Payor contact number.
• paymentEmail - Payor email address.
• paymentOriginal - Is the address belongs to the payor? Or someone else address?
• paymentDate - Date the payment is made.
• paymentBank - Selected bank in Direct Bank Debit payment mode.
• paymentCardType - Select card type in Credit Card and Charge Card payment mode.
• paymentCardNumber - Respective card number.
• paymentCardHolder - Card holder name.

For more SQL flow behind the scene, you can refer to the previous post, I have updated it with additional back-end info.