Correct approach to add additional products RRS feed

  • Question

  • I have the following requirement:

    When a customer adds an item to their cart, i need to add related products for this product. 

    Example 1 - I want to buy a TV, i add it to my order. Now i need to give the option of Extended Warranty, TV Setup etc. If i want to go ahead with these additional products i leave them in if not i will delete them.

    Example 2 - I want to buy a Fridge Freezer, add it to my order and the option of Fridge Installation, Recycle Old Fridge are added to purchase. Again i can proceed to checkout and continue buying or remove them.

    I have the following tables in my database:

    AdditionalItems (related to the Products table)

    AdditionalItems contains ID, ProductID, AdditionalProductID (both linked to Products as at the end of the day the additional items are also products)
    Orders contains Id, UserID, date, SessionId, Total etc
    OrderItems contains Id, OrderID (link to above table), quantity, SinglePrice, TotalPrice etc

    The admin of the site would select a product i.e. TV and then select all products that he would like to sell alongside with the products added to AdditionalItems table. This way i know which products have which additional product.

    What i need is a design which would allow multiple additional products to be added for each item when the customer add a product to their order.

    The way i think i should approach this is to add another table

    AdditionalOrderItems - OrderItemsID, AdditionalProductsID (link OrderItemsID to OrderItems and AdditionalProducts to Products table)

    Does this seem to be along the correct lines or is there a better way of doing this?

    Edit 1: I was also thinking of adding OrderID to the AdditionalOrderItems so i can query the all Additional products added for a particular order - but this is starting to seem a little repetitive?

    • Edited by Pure Deal Monday, June 12, 2017 10:09 AM
    Monday, June 12, 2017 10:03 AM