0

I am seeking to create an online store so I got to the database definition. I want to have as few tables as possible, so I've got them narrowed down to:

  1. Users
  2. Products
  3. Orders
  4. Cart_Items

I got to the following schema: image

The logic flows like this: For each product added in the cart, a new Cart_Item entry is created. But the Cart_Item entry needs to fill in the foreign key that references the Order table, which is cartItemOrderID. Since there's no order created, it'll have to be null at first. Once the user adds all their desired items in the cart, they place the order, and a new Order is created, so now I have the orderID. How would I pass this orderID to the entries in the Cart_Item table, since there's no way to tell they were placed by the current user?

I'm missing something huge but I can't put my finger on it.

1

I think your main challenge is the requirement that the Order is in between your User and Cart_Item.

I am assuming you are planning on storing items put in the shopping cart in Cart_Item (items planned to be purchased but not currently purchased. Also there is only 1 list of items pending purchase per user). And then items actually purchased by the user (coming from he shopping cart) will land in Order. In my opinion, there should not be any kind of link between the "order" and the "shopping cart". If there was, you would need to store some kind of Cart_ItemId on the Order table, not an orderId on Cart_Item. If you were to keep a Cart_ItemId, you would then have to have some kind of shopping cart history, which to me, seems redundant since that data would be saved in your "order history" though Order. You may have another purpose that I am not thinking of so let me know if I am missing something.

Assuming my understanding of what you are looking for is correct. I believe this below schema is much closer to giving you what you are looking for, and getting you around this Foreign Key issue. (I apologize, your relationship diagram was so much nicer than mine. Trying to make it work in Visio instead):

Schema Recommendation

Transferring data from your Cart to Your Order could look something like this:

CREATE PROCEDURE [FinalizeOrder] (@UserID INT, @OrderDetails VarChar(500))
AS
BEGIN
    INSERT INTO Order_Item
    (
        userID,
        orderProductId,
        quantity,
        price,
        [date],
        details
    )
    SELECT
    CI.userID,  --userID,
    CI.cartItemProductID,   --orderProductId,
    CI.quantity,    --quantity,
    P.price,    --price,
    GETDATE(),  --[date],
    @OrderDetails --details
    FROM Cart_Item CI
        INNER JOIN Product P
            ON CI.cartItemProductID = P.ProductId
    WHERE CI.userID = @UserID

    DELETE FROM Cart_Item
    WHERE CI.userID = @UserID
END

Now if it was me, I would create Order and OrderItems tables. That way you don't duplicate data like the User, or address information on each item. I know you want to limit tables that are created, but I think that will help you a lot. If that is a route you end up going, than you will have a schema a little like this:

Schema Recommendation

For a quick example. Lets pretend I am a user (UserID 13 because that is my favorite number). I fill out a cart with the 5 items. The records from the Cart_Item table looks like:

+------------+--------+-------------------+----------+
| CartItemId | UserID | CartItemProductID | quantity |
+------------+--------+-------------------+----------+
|          1 |     13 |               123 |        1 |
|          2 |     13 |               456 |        2 |
|          3 |     13 |               789 |        5 |
|          4 |     13 |            123456 |       77 |
|          5 |     13 |                56 |       45 |
+------------+--------+-------------------+----------+

When I hit the last "submit" in the check out process we would then write 1 record to Order and 5 rows to Order_Item. Those rows would look something like:

[ORDER]
+----------+--------+----------+--------------------------------------------------------+
| OrderId  | UserId |   Date   |                        Details                         |
+----------+--------+----------+--------------------------------------------------------+
| 98564745 |     13 | 4/6/2020 | Order Placed by Kirk Saunders on 4/6/2020 for 5 items. |
+----------+--------+----------+--------------------------------------------------------+

[ORDER_ITEM]
+----------+--------------------+----------+-------+
| OrderID  | OrderItemProductID | Quantity | Price |
+----------+--------------------+----------+-------+
| 98564745 |                123 |        1 |     1 |
| 98564745 |                456 |        2 |   1.5 |
| 98564745 |                789 |        5 |  7.98 |
| 98564745 |             123456 |       77 | 85.99 |
| 98564745 |                 56 |       45 |  1065 |
+----------+--------------------+----------+-------+

A Stored Procedure to handle this might look like:

CREATE PROCEDURE [FinalizeOrder] (@UserID INT, @OrderDetails VarChar(500))
AS
BEGIN
    DECLARE @OrderId INT

    INSERT INTO [Order]
    (
        userID,
        [date],
        details
    )
    VALUES
    (
        @userID,    --userID,
        GETDATE(),  --[date],
        @OrderDetails --details
    )

    SET @OrderId = scope_identity()

    INSERT INTO Order_Item
    (
        OrderId,
        OrderItemProductID,
        Quantity,
        Price
    )
    @OrderId,   --OrderId,
    CI.cartItemProductID,   --OrderItemProductID,
    CI.Quantity,    --Quantity,
    P.Price --Price
    FROM Cart_Item CI
        INNER JOIN Product P
            ON CI.cartItemProductID = P.ProductId
    WHERE CI.userID = @UserID

    DELETE FROM Cart_Item
    WHERE CI.userID = @UserID
END

Hopefully this helps. Let me know if I need to explain something better or if this doesn't answer your question.

| improve this answer | |
  • Thank you very much for your input. My thoughts: You said a user can only have one Cart_Item. If a user placed an order, and an order can contain multiple Cart_Item, then I suppose an user can still have access to multiple items. And, yes, my logic initially meant that Cart_Item entries would lead to Order entries, that was the problem initially, because I had Cart_Item entries, but I had no Order they could refer to. – Radu Apr 5 at 8:43
  • Regarding the model you proposed: What exactly is the table Order_Item? When would you add an entry to this table? Also, how would the logic work if there was no direct relationship between Cart_Item entries and Order entries? – Radu Apr 5 at 8:44
  • @Radu, I apologize if my answer was confusing. I will certainly change the wording in the answer to make it more understandable. When I made the comment about a user only has one cart, I was referring to how when someone it utilizing this platform. I am assuming there will only be one list of items which they are planning on purchasing. I see this as different in your original version, since the cart was tied to the order. Which i my mind meant that you would effectively be keeping a "cart history" in the Cart_Item table. That may not be been what you meant, but that is what I read. – Kirk Saunders Apr 6 at 17:15
  • @Radu. The intent of the Order_Item table is to hold the items associated with a given order. When the user finishes the order process. One record would be written to Order which holds things like the shipping address, the date the order was placed, etc. Then each item that was on that order will be written to Order_Item. That way things like: the user who placed the order, when the order was placed, the address it would be shipped to, etc... is not repeated on for each product. But that may be more normalization than you want. That is just how we handle orders at my work. – Kirk Saunders Apr 6 at 17:18
  • It was my bad, I did not explain good enough. The Cart_Item table contains rows of Products in a User's cart. Once the order is placed, the Cart_Item table would become empty. That's why it was tied to the Order table, it would read as "One Order has one-or-multiple Cart_Items" (meaning a single order has one, or multiple chosen products). I do not want to keep a history of the carts, once a cart is bought, the info should get archived to the Order table. So let's say the Cart_Item table will be cut off (because it seems it has the same functionality as Order_Item in your example). Then.. – Radu Apr 6 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.