2

I'm working on an order system for my online shop. I have 2 tables:

  1. products, storing info about products
  2. orders, storing general id's & infos of customer orders.

Now I want to have a way to store complex customer orders in the database. I need something that will let me know how much of each size (S, M or L) of each product is in an order.

The tricky part is that I want to be able to add/edit/delete products (of course without affecting orders from the past), so the method should be flexible

How should I go about this?

  1. a separate table for every order, with products as rows?
  2. one table for all orders, with products as columns?
  3. some other option?

Thanks!

2 Answers2

6

Depends on your goals for your cart. For instance, do you want to allow guest purchases? i.e. where a user does not need to login in order to make a purchase?

The attached image is a design I have been working on and it goes like this:

  1. A visitor selects products from the site and adds these to a session cart (just a place to temporarily store the products, their quantities and their prices etc.)

  2. Once the customer is ready to check out, we create the order, the order person and the person_address (where the product must be delivered to) and add the items to the order_item table. All this information is added by the customer in the checkout page.

  3. The final step is then to offer the payment methods: paypal, credit card, etc.

What I like about this design is that users have no obligation to register with us. Order_person acts as a kind of interface between users and orders. If do register, we simply link order_person to the user table...

I have included a sample front end of the checkout page too.

Product Order Database Design

enter image description here

HappyCoder
  • 5,985
  • 6
  • 42
  • 73
  • it was really helpful! one little thing that I don't understand is what does rbu_user_id mean? – Philipp Mochine Aug 01 '18 at 05:21
  • 1
    Good question, there is a user table as well (role_based_user) which points to the order person (I should have included it for clarity). When a user registers they are stored in the user table and when they make a purchase the details are linked to their order_person (which can vary). If someone decides not to register, their details are stored in the order person table. – HappyCoder Aug 02 '18 at 06:48
  • There are scenarios where user might initiate a payment, cancel it and go back to try to edit the order before proceeding to initiate another payment. If a order, according to your approach, is created prior to completion of payment, how do it get edited? – Alika Matthew May 25 '21 at 14:43
  • The order does need to be created in order to link the payment to an order ID. If the user wishes to update an order, it should not be too difficult to create a new order... – HappyCoder Jun 08 '21 at 18:15
5

At the very least you need:

Products (one row per product)
    ProductID
    Size

Orders (one row per order)
    OrderID

OrderDetails (one row per product per order)
    ProductID
    OrderID
    Size

Note that each 'size' is its own ProductID. You'll probably want to have yet another ID that groups products that are the same 'base' product, but in different sizes.

So if Order #1 has three products, and Order #2 has four, then OrderDetails will have seven rows:

OrderID ProductID Quantity
1       234       2
1       345       9
1       456       30
2       432       1
2       234       65
2       654       8
2       987       4
egrunin
  • 24,650
  • 8
  • 50
  • 93