Best way to tackle inventory control? I have several ideas...need help.
Ok i need to tackle setting up some inventory control - but - im unsure what idea to move forward with. i have items that have both size and color options.
i have 2 tables in the database
1 table is the main product (SHIRT)
1 table is the subitems with the size/color/inventory (Size: LARGE, Color: BLUE, Inventory: 5)
----IDEA 1-------
on the product display page i plan to display only items that have > 0 inventory. My thought was to use a JOIN statement in the recordset to pull info from what has been ORDERED already. IE INVENTORY minus ORDERED = available amount.
My issue here is - HOW do i do the subtraction?
I have used before SUM, MAX, COUNT etc - but how do you subtract? It doesnt seem there is any function called DIFFERENCE.
----IDEA 2-------
another idea was to update the inventory field after the order is placed. (IE have this occur on the success page). However - this seems to be tricky when there is more than 1 item that is ordered. Im stumped as to how i would pull the recordset of multiple items - or do one at a time and have a recordset for each.
I need some guidance on which is the best idea, if there are better ideas, and HOW TO MOVE FORWARD ON WHICHEVER IS THE BEST.
Please assume I am dumb as a rock and give me the extreme basic 123 steps of how I should approach this.