1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

New schema, how is it please?

Discussion in 'Databases' started by JoshuaEir, Jun 11, 2020.

  1. #1
    Firstly, here is an image of what I am talking about. It is an ecommerce site like Amazon for people to log in and sell things. It lists product by using a search engine script.

    My question is about a new table that I am adding and I am considering the schema. Would you help please?

    Here are my tables that include my newest development (the holdforpurchase is new). Jeet helped me with the first three.

    PRODUCTS table:
    productID (primary auto increment key)
    image (varchar)
    description (text)
    quantity (int not null)


    ORDERS table
    orderID (primary)
    productID
    customerID
    quantity_ordered
    order_date (timestamp)

    CUSTOMER table
    customerID (primary)
    customerEmail
    customerPassword
    customerName
    etc etc

    HOLD_FOR_PURCHASE
    holdid (primary auto increment key)
    productid
    image (varchar)
    description (text)
    quantity (int not null)

    So far, the holdforpurchase table is linked to Products table. I am thinking the connection would be one to one and should the product table have a foreign key from holdforpurchase? It is still designed the same way, created on selection , and deleted on purchase.

    Thank you, JoshuaEir

    digitalpoint.jpg
     
    Solved! View solution.
    JoshuaEir, Jun 11, 2020 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #2
    Can you explain the HOLD_FOR_PURCHASE table a bit more, it has images in it?

    I'd have thought HOLD_FOR_PURCHASE would be a status of the Order table (or more precisely orderlines) if it's purpose was to remove items from stock.

    An order could be
    • New
    • Paid
    • Complete
    • Hold
    Each orderline could be
    • Pending
    • Backorder
    • Hold
    • Shipped
    But I'd probably only have Hold on one of those.

    I know some warehouses do let customers buy goods and they hold them until the customer is ready for them. That frees up the customers warehouse space while giving the seller the certainty that the stock is sold and the cashflow. I'd need to know more about your sales processes before advising one way or the other.
     
    sarahk, Jun 11, 2020 IP
    JEET likes this.
  3. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #3
    If you are planning something like Amazon, then this database structure will not do.
    Your products table is missing so many fields.
    Like @sarahk pointed out in previous thread, "invoiceID" is missing from orders table.
    Customer table itself is just holding basic customer info...

    You will need many more tables and many more fields in existing tables to create a basic version of Amazon.

    For example, products table could look something like this:

    1. productID auto increment primary
    2. sellerID bigint(18) connection with sellers table to know who listed this product
    3. quantity int
    4. productName varchar
    5. productDescription text
    6. productImage varchar (will only store filename. File itself will be stored in images folder)
    7. price decimal
    8. rating int
    (above 2 columns can be used to sort products based on cost or rating submitted by buyers)
    9. dateAdded timestamp
    10. lastUpdated timestamp
    index( sellerID, quantity, productName )
    INDEX( price )
    Index( rating )

    These would be just a few basic things needed in this table alone.

    Your primary query which lists (or searches) products might look like this:

    select p.*
    from productsTable as p
    left join sellersTable as s
    on p.sellerID = s.sellerID
    where s.status='active' and p.quantity>0
    and p.productName like '%$searched_term%' (used if its a search query)
    group by productID
    order by rating desc
    limit 0, 20
     
    JEET, Jun 11, 2020 IP
  4. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #4
    The site I am planning will not have any orderline. As far as orders I was thinking just a purchase and now maybe a return. Everything else would be done manually by the client.

    The idea of holdforpurchase is being misunderstood. This is not a status. This table is a temporary holder of all the orders with quantities that is created when the product is selected. This means that the order is also sticky and the purchaser has the priority to get the order. When the purchase is completed the holdforpurchase records are also deleted. At this point the order is made which will be a purchase record. Holdforpurchase means that the records are held in this table until they are purchased.

    This brings me back to the original question, could this be a one to one relationship with product because products has a held table and held table has products.

    Josh

    And oh, there would be no foreign key in product table?
     
    JoshuaEir, Jun 12, 2020 IP
  5. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #5
    I don't understand what you are trying to do with the "hold" table.
    Suppose a seller lists a new product with 10 quantity.

    So we added a row in main products table

    When someone ordered 2 out of these 10 quantity, we deduct "quantity" column in products table.
    Now products table has 8 quan for this product, and orders table has 2 quan with an "open" status order.

    When the order is processed (delivered), then status of order will change in orders table to "closed", no change in products table.

    If the order gets cancelled, then status of orders table changes to "cancel", and 2 quantity is added to products table.

    What is the role of "hold" table in between?

    In an ecommerce platform, try not to delete records. These are logs, for future invoicing, billing, tax and audit purposes etc...
     
    JEET, Jun 12, 2020 IP
    sarahk likes this.
  6. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #6
    There is no need, Jeet, I understand! :)

    I do have questions though. What is orderline, and how is it related? Are the logs open, closed, and canceled using the same row in an order table (with nulls in some columns)? Also, it seems like there should be a current state saved somewhere to check the orders.

    Thank you,
    Josh
     
    JoshuaEir, Jun 12, 2020 IP
    JEET likes this.
  7. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #7
    When you go to the supermarket and buy bread, milk, and eggs you are given a receipt with 3 items on it. That's the equivalent of your order with 3 orderlines.

    I currently maintain a system that has one product per order and most of the time that's fine, but there are other times where it's a hassle and I'd have been better off building a shopping cart type of system. Once you allow more than one item to be sold there is the risk that they may not be in stock at the same time and will be shipped individually.

    I also don't understand the holds table. It seems to be compensating for a desire not to write complex SQL queries. By the time you've added audit logs the whole thing a change of status that is audit logged really does make more sense. I've read, and re-read your posts and can't see the business rule that requires this. I've worked with other inventory systems too and never seen anything like it.
     
    sarahk, Jun 12, 2020 IP
    JEET likes this.
  8. #8
    @JoshuaEir
    I think there is some confusion in your mind about the core processing of cart itself.
    Below is sample schema and queries
    Tables are not complete, you might want to add more columns depending on what you want to store.

    productsTable
    productID, customerID, quant, productName, productImage, productDesc, price, rating, dateAdded

    OrdersTable
    orderID, productID, cartID, orderStatus, customerID, quant, actualPrice, discountPrice, dateAdded

    customersTable
    customerID, customerStatus, customerName, etc etc

    In this structure I'm allowing all customers to also post products of their own and sell them.
    If you want to change it, either make a separate table for sellers, or make a column in customersTable (typeOfCustomer) which can store "seller" or "regular" depending on if this is a seller or just a buyer ability user of site.

    Query To list product in search:
    select p.*
    from productsTable as p
    left join customersTable as c
    on p.customerID = c.customerID
    where c.status='active' and
    p.quant>'0' and productName like '%searched_term%'
    group by p.productID
    limit 0,20
    Code (markup):

    Suppose user added a product in cart to buy it:
    cartID is generated by your PHP script,
    something unique,
    something that will not get repeated in future ever,
    something for this customer only,
    something that will not change till this customer makes payment.

    <?php
    if(!isset($_COOKIE['cart'])){
    $cartID= md5(time().$_SERVER['REMOTE_ADDR']. rand(100, 10000));
    setcookie( "cart", $cartID, time()+(3600*24*90), "/" );
    }else{
    $cartID= $_COOKIE['cart'];
    }
    
    //suppose generated cartID is "123456"
    ?>
    Code (markup):

    query to add product:
    insert into ordersTable
    ( productID, cartID, orderStatus, customerID, quant, actualPrice, discountPrice, dateAdded ) values
    ( '3', '123456', 'open', '$customerID', '2', '5.00', '3.00', 'date' )
    Code (markup):
    "3" is productID which they are adding to cart
    "open" is the status of the order
    "2" is the quantity
    $customerID - this you will get based on your login system, from cookie or session



    Lets say they added 2 more products like this, different productID (4 and 7)
    Same query will repeat, only productID, quant, price, discountPrice will change

    insert into ordersTable
    ( productID, cartID, orderStatus, customerID, quant, actualPrice, discountPrice, dateAdded ) values
    ( '4', '123456', 'open', '$customerID', '1', '15.00', '13.00', 'date' )
    
    
    insert into ordersTable
    ( productID, cartID, orderStatus, customerID, quant, actualPrice, discountPrice, dateAdded ) values
    ( '7', '123456', 'open', '$customerID', '1', '25.00', '23.00', 'date' )
    Code (markup):

    So now this person has 3 products in their shopping cart.
    shopping cartID='123456'

    They proceed to checkout
    payment is made via your payment processor.
    You can either deduct quant from productsTable here itself,
    or you can deduct in back end when order is actually processed. (suppose we use this one)

    In back end the sellers who are selling those 3 different products, they updated the status of each order.

    update ordersTable
    set orderStatus='done' where orderID='1' limit 1

    //get quant
    select productID, quant from ordersTable where orderID='1'
    $productID and $quantity variables of PHP script store this info now
    
    update productsTable
    set quant= quant - $quantity where productID='$productID' limit 1
    Code (markup):


    Notice that the "status" of this order has changed.
    This is your current status tracking.

    query to show status of their order to customer (buyer)

    select p.*, o.*
    from productsTable as p
    left join ordersTable as o
    on p.productID = o.productID
    where
    cartID='123456'
    group by orderID
    Code (markup):
    This query will have all 3 products they added in cart, along with orderStatus and all other info.
    Use a HTML table to display productName and orderStatus
    They added product "3, 4, 7" in this cart.

    product three | done
    product four | open
    product seven | open
     
    Last edited by a moderator: Jun 12, 2020
    JEET, Jun 12, 2020 IP
  9. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #9
    Okay, Jeet I'm still digesting your post, thank you!

    Allright, I am now able to explain a reason for the holdforpurchase table. The idea behind the table was that it would be a solution to a user becoming disconnected (or aborted) from the system. The user gets the rights to the selected item immediately when selecting. It is deleted in the product and added to the holdforpurchase. If the user disconnects from the server the callback function will be called and replaces it back into the product table.

    I am still pretty confused about how to understand working with disconnects!
     
    JoshuaEir, Jun 13, 2020 IP
  10. mmerlinn

    mmerlinn Prominent Member

    Messages:
    3,197
    Likes Received:
    818
    Best Answers:
    7
    Trophy Points:
    320
    #10
    I think such a table would have been better named "willcall" or "wishlist" or something similar that is in common use rather than "holdforpurchase" which no one really understands without an explanation.
     
    mmerlinn, Jun 13, 2020 IP
    JEET likes this.
  11. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #11
    Even then, it's still a cart or order that is "new" and when I return I can pick it back up.

    Orders that are "new" will be voided after x hours.

    I abandon carts frequently when I get to the calculate shipping bit and find the shipping cost negates the great deal I thought I had. I'd be appalled if sites like eastbay actually held those items aside hoping I'd come back.
     
    sarahk, Jun 13, 2020 IP
    JEET likes this.
  12. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #12
    I'm not really understanding your last post. The problem is that the order could be reserved and than that user could disconnect and the database would be set for that person as purchasing the item.
     
    Last edited: Jun 13, 2020
    JoshuaEir, Jun 13, 2020 IP
  13. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #13
    @JoshuaEir
    I think I get your point now, about user getting disconnected.
    This is why the cartID is for.
    You have 2 options here.

    1. Update the status in ordersTable, set it to "paid" when payment is made.
    Until then, order is simply an entry in database, does not means anything. Can be used to show the customer their open orders.
    These orders are not processed until status changes to "paid". Currently its "open" or "new".

    2. Second option is, make a "carts" table, like this:

    id, status, customerID, cartID, date
    (index on status, customerID, cartID )

    When a customer comes to website, and adds first order in their cart, then 2 queries are run.

    insert into ordersTable
    ( productID, cartID, orderStatus, customerID, quant, actualPrice, discountPrice, dateAdded ) values
    ( '3', '123456', 'open', '$customerID', '2', '5.00', '3.00', 'date' )123

    This one runs only once, when first order is added:

    insert into cartsTable
    ( status, customerID, cartID, date ) values
    ( 'unpaid', '$customerID', '123456', 'date' )

    After this customer can keep adding products to their cart, and only ordersTable query gets executed.

    On payment page, when payment is successful, then change the status of cartsTable to "paid".

    update cartsTable
    set status='paid' where status='unpaid' and customerID='$customerID' and cartID='123456' limit 1

    If you are not using this table, and using "only" ordersTable, then this query: (will be slower, and will have to run without "limit")
    Bad idea to run query on a large table without "limit"

    update ordersTable
    set status='paid' where cartID='123456'


    Now it doesn't matters if user gets disconnected.
    As long as you have cartID, you can pull the records from database and show the cart to the customer again.

    Suppose you are using "cartsTable" method.

    select status, cartID
    from cartsTable
    where status='unpaid' and customerID='$customerID' order by date desc limit 5

    This will list all their recent carts which are unpaid.

    Use "limit 1" to get the most recent one, and to recreate cartID cookie/session.
     
    JEET, Jun 13, 2020 IP
    sarahk likes this.
  14. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #14
    I wasn't really concerned about the client getting their order again. If the client has only a new status than at the time of purchase there could be a bunch of orders that need to be readjusted because the inventory has changed.
     
    JoshuaEir, Jun 14, 2020 IP
  15. mmerlinn

    mmerlinn Prominent Member

    Messages:
    3,197
    Likes Received:
    818
    Best Answers:
    7
    Trophy Points:
    320
    #15
    Why change the inventory before the customer buys? Wouldn't it be simpler just to flag one in your inventory list with a flag that automatically expires after a certain time?
     
    mmerlinn, Jun 14, 2020 IP
    JEET likes this.
  16. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #16
    Thank you for the reiteration.


    I feel like I'm understanding everything better, however, doesn't this go against your recommendation to not delete records?
     
    JoshuaEir, Jun 14, 2020 IP
  17. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #17
    @JoshuaEir
    At one place or another you will have to adjust the inventory if a cart was filled and then not paid for.
    This choice is yours where you want to do it.

    Think of a shop.
    You liked something, and you picked that item from shelf and dropped it in your shopping cart.
    Now that item is not available to other people shopping in the store. You have claimed it.
    When you went to the cash counter, you realized that you did not had enough money to pay for all you have picked.
    You pay for important things, then store workers place the rest back on the shelf, making it available to other customers again.

    Ecommerce store is also like this.
    You have 3 options here.

    1. Deduct the quantity from productsTable (your shelf) as soon as customer adds it to shopping cart.
    In this option, you will have to expire the unpaid cart after a very short while, otherwise you will run out of inventory very quickly, and nothing is paid for.
    This will be really messy.

    2. When payment is being made.
    In this case, there is small possibility that 2 people ordered same thing in cart, which had only "1" inventory left.
    The person who made the payment first, his order will be shipped by seller.
    Other one, who paid later, will have to wait a bit longer for delivery, or their payment is refunded by seller.

    3. When seller finally ships the product.
    Almost same as option "2", however, can create too many waiting orders...
    Fortunately, only a problem on a very high traffic site, with a product in high demand.

    Option 2 and 3 will not create too much problem normally.
     
    JEET, Jun 14, 2020 IP
  18. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #18
    We are still not removing the entry from the database. We are keeping the entry, only changing a status.
    Data is still there in database, can be pulled out for analysis or tax or something else later.
    Deleted records are gone, nothing can be done there...
     
    JEET, Jun 14, 2020 IP