I need assistance with developing the reports section for my e-commerce app. So far, I have completed the purchase process with the following database structure:
Items
Restaurant
Name
Price
Items Cart
Restaurant
Items (List of items)
Order ID
Quantity
Subtotal
Order
Restaurant
Order ID (items cart)
ItemsCart (List of ItemsCart)
Total Price
I want to create a table that shows the quantity sold and the total revenue generated per product.
Here are my questions:
Do I need to make any modifications to my current database structure to achieve this?
I have tried various approaches but haven’t been able to achieve the desired results. Any guidance or examples would be greatly appreciated.
Items Cart should have the Quantity, Total and Item (not Items, since an individual item will be added). The Order table should have the ItemsCart as a list.
You can remove the Total Price from Order since you already have it in the Items Cart as the sub-total, so storing the same info twice seems redundant.
You can remove Restaurant from Items Cart since you already have that in Items as well as the Order
Apart from that, you can achieve the per-product quantity and total revenue by Doing a search for Items Cart where the Item is the current item you want to search for. You’ll get a list of Item Carts that you can add over for the Quantity and Total
Thank you very much for your response. It has been very helpful, but I still have a problem bringing the data to the table.
Let me tell you a bit more about how I designed this process:
Users can add items in the quantity they desire. Adding or removing an item triggers a workflow that adds or removes items from the cart.
Once in the cart, users can delete, add, or remove items.
Once the user has the desired order, they can execute the order.
If I do a “do a search for” “items cart” I will get incorrect data, as it includes not only order data but also data from carts that have been abandoned.
How can I bring to a table the quantity and total price per product of the orders and not all the carts?
Create another data type called Cart, which has a list of Items Cart in it as a list (you’ll need Items Cart since it specifies the quantity and the Item); or
Create the Order whenever a user add a Item Cart and add a Status field for it. Filter by Status (the fields can be In cart, Pending payment, Completed and so on)
Thank you again, Zeroic. I opted for your second suggestion about adding a “status” field, and now, after a user clicks to generate an order, the items in the cart are updated to show “Completed” in the status field.
So far, this has helped me calculate the totals per product, but I still can’t get the table to correctly reflect the 3 columns. As you can see in the attached image, the table correctly shows columns 2 and 3, which are the units and revenue per product. However, column 1 should only say 1 item, for example, “Americano” or “Chocolate Ice Cream,” but it shows repeated items.
