CODEWITHSUNDEEP
sqlsql-server
Yamen Massalha
Yamen Massalha

Reputation: 13

SQL many orders for some user

I have asp.net website (Language :C#). It's a store (User can order, Add to cart), every user have to sign in before doing that. Example: user "X" logged in , and added a product ("Iphone 7") to his cart. user "Y" logged in , and added the same prodcut (Iphone 7") to his cart. how can I make a relationship in the SQL between the "Users" table and "Products" table that I can show in "My Cart" page the products that every user have added to his cart ?

Upvotes: 0

Views: 94

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If I understand correctly, you would have two tables called Carts and CartItems, something like this:

create table Carts (
    CartId int identity primary key,
    BuyerId int references Users(UserId),
    CartDate datetime,
);

create table CartItems (
    CartItemId int identity primary key,
    CartId int references Carts(CartId),
    ProductId int references Products(ProductId),
    int quantity,
    price money
);

In other words, you want two tables, not a separate table for each user. The information just goes in as separate rows in these tables.

This ensures:

  • A user can have multiple carts, even on the same day.
  • Carts can have multiple items.
  • All items in a Cart are identified with that cart.

Upvotes: 1

Related Questions