This is not about helping with code, this is more those who are experienced MySQL developers giving me their opinions on the way I am developing.
This is all in relation to an ecommerce application. I have been using a version that I wrote starting in 2012, I am now re-writing it, and I want to improve the way the software works. It is a booking system.
1 – Stock Checking.
Currently I have been doing stock levels by when the user adds to their cart it takes one off the total ie. -1. However this is getting messy with sales not being followed through etc. So I am thinking I will change to a COUNT of the number of people who have ordered it. I could also add a timestamp to the orders just added so the COUNT includes these as bought if they were added in say the last 5 minutes. Any thoughts on this?
2 – Purchasing for a year
Some of my clients customers want to order for an entire year. This means that they wish to order 5 products, in 40 orders (1 order per week) and sometimes with 2-3 children. This means that when they go to the checkout there are 40X5X2 = 400 product items to check, for just one customer. This is killing my server. With my change above will these SUM queries be more efficient or is stock checking on such a large scale per customer instantly just a bad idea?
3 – DB connections.
I run 1 copy of the application software on AWS EC2, this connects to an RDS small DB. I check my connections and often they say 3-4 connections, despite many, many orders coming in. Does the application software only show as one connection to the DB even if multiple clients are using the software?
4 - Product links to orders
I have normalized the DB in the sense I have a table of Products, a table of Orders and a table of Ordered items
Withing that Ordered items is the link between Orders and Products. That table is huge, 60,000 records. Confirm for me this is the correct logic for doing this? It seems like a very large table to have indexed!
Thanks very much for your expertise’s