How to fill in a foreign key value after creating the first entry of the table the foreign key is referencing

database-designschemasqlite

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.

Best Answer

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.

Related Question