Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jun 2009
    Posts
    146
    Thanks
    3
    Thanked 21 Times in 21 Posts

    General MYSQL questions

    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

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,131
    Thanks
    2
    Thanked 328 Times in 320 Posts
    #1 - stock amounts are only modified when the item is actually pulled and shipped (or you receive a quantity to re-stock that item.) Prior to that point in time the item is still in your stock quantity. If an item is in someone's cart, but the order has not been placed/finalized, the item is at best 'reserved'. Once the cart contents has been converted to an order, the items become allocated to an order.

    At this point you need to decide what you want to do when the demand is greater than the available quantity. If the available quantity is all that is ever going to be available (a one time item), you would query for the amount in stock, the amount allocated to actual orders, and any amount reserved in currently active carts (based on a date/time and a time limit) to come up with the available quantity. This of course requires that you store your cart contents in a database, rather than it being just in a session. If you can obtain more of the items (they are not a one time item), you would allow more to be ordered than the available quantity and you would indicate which items in a cart will be backordered (requested quantity exceeds the current available quantity.)

    #2 - I'll assume you mean when you are querying for the product information to display and that you are doing it one id at a time? You would get all the product id's from the cart and run one query to get all the corresponding product information in one query. If your cart is also stored in a database table, this is even simpler since you can just join the cart with the product information in one query.

    #3 - The concurrent number of visitors would determine how many concurrent database connections there are. Even with many many orders being placed, they are most likely sequentially accessing the database, with one request finishing its processing and releasing its connection before the next request occurs and opens its connection.

    Your site would need to be really busy at one instance in time and take a long time for the script to run before you would see the number of concurrent database connections go up.

    #4 - 60k rows is not huge. Tables with millions of rows are not uncommon. An unsigned integer autoincrement column can keep track of 4,294,967,295. You should have no problem with 10's of millions of rows before you need to start using things like table partitioning to segregate data.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Regular Coder
    Join Date
    Jun 2009
    Posts
    146
    Thanks
    3
    Thanked 21 Times in 21 Posts
    Thank you for taking the time to reply. Its good to get expert feedback when you are so early on in the game.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •