Which way is a better structure for users table in a shopping project - SQL Server

Question

I think it will be faster, but if its a better database design and structure I'm not sure,
Should I create a field in Users table which indicates if the user has a shopping cart or not, and only if its TRUE - 1 than to search for his OrderID in the ShoppingCart table ?
Or just to search anyway for OrderID and UserID when the user is logging in ?

Example

[Users]
(
UserID INT IDENTITY(1,1) PRIMARY KEY,
EmailAddress NVARCHAR(320) UNIQUE,
PasswordHash VARCHAR(32),
FirstName VARCHAR(256) NOT NULL,
LastName VARCHAR(256) NOT NULL,
HasCart BIT DEFAULT 0
)

[ShoppingCart]
(
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    UserID INT FOREIGN KEY REFERENCES [Users](UserID),
    CreatedDate DATETIME DEFAULT(GETDATE())
)

Answer 1

You should put the UserID on the ShoppingCart, and index it.

create table ShoppingCart
(
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    UserID INT FOREIGN KEY REFERENCES Users(UserID),
    INDEX ix_UserID (UserID),
    CreatedDate DATETIME DEFAULT(GETDATE())
)

For old versions of SQL Server (2012 and before) you must create the index with a seperate statement:

CREATE INDEX ix_UserID on ShoppingCart(UserID)

This will support efficient lookups of ShoppingCart by UserID, and if you delete a User SQL Server won't have to scan ShoppingCart to find that User's ShoppingCarts.

Answer 2

No need to create an additional column to indicate if the user has a cart for performance reasons, only do so if it makes logical sense to your use case.

It would actually be less performant (albeit likely negligible) to query the Users table first to then query the ShoppingCart table after, since it's two operations that would lock two tables to accomplish what could be done with one operation of always directly searching the ShoppingCart table.

As long as your ShoppingCart table is indexed on the fields that cover your search query (e.g. the OrderID if you're querying by that field), the performance (especially for only pulling one, or a small amount of records at a time) will be extremely quick. This is because indexes store the data logically in B-Tree data structures which has an O(log(n)).