locked
How to connect cart table and order table using key RRS feed

  • Question

  • Hi All,

    My Question is i have a two table Cart and Order Table,

    i need to connect both table using key.

    we can add multiple product in one cart, but when i click place order button, data which is available in cart it become order,

    see the below table fields-

    Cart-

    CartId
    ProductId
    ProductName
    Quantity
    BasePrice
    TotalPrice
    Image

    Order-

    OrderID
    ProductId
    CustId
    ProductName
    Price
    Quantity
    Name of the Person
    Shipping Address

    My question is how to connect both tables,so it allows me to create one order for two product using Cart.

    Can someone explain me.

    Thanks& Regards,

    Jyotsna












    Tuesday, February 28, 2017 10:46 AM

Answers

  • At some point in time the cart will become an order.  You will need to add a new column to your order table as CartID and populate it with the cardid value.

    The way this is traditionally done is to have an order table which is a parent and then an order details table which is a child of your order table. It looks like your cart table is the child of the order table in this case.

    Tuesday, February 28, 2017 12:17 PM
  • No, you need in Cart table have  at least 2 columns

    CREATE TABLE Cart (CartID VARCHAR(5), ProdID VARCHAR(5))-- Probably a PK constraint to enforce uniqueness 

    Then insert the data 

    INSERT INTO Cart (CartID ,ProdID ) VALUES ('C001','P001')

    INSERT INTO Cart (CartID ,ProdID ) VALUES ('C001','P002')

    Now we have an one cart with two orders, so same create table ORDERS with two columns (OrderID, CartID)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 1, 2017 6:21 AM
  • You need create PK constant on CartID

    CREATE TABLE Cart (CartID VARCHAR(5) NOT NULL PRIMARY KEY, ProdID VARCHAR(5) foreign key references Product(((ProdID)))


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 1, 2017 9:54 AM
  • Create Table Product(ProdID VARCHAR(5) Primary key,Name VARCHAR(5))

    CREATE TABLE Cart (CartID VARCHAR(5) not null, ProdID VARCHAR(5)  not null foreign key references Product(ProdID))
    ALTER TABLE dbo.Cart ADD CONSTRAINT
    PK_Cart PRIMARY KEY CLUSTERED 
    (
    CartID,
    ProdID
    ) ON [PRIMARY]


    Create Table Orders(OrderID VARCHAR(5) Primary Key,CartID VARCHAR(5), ProdID VARCHAR(5))

    ALTER TABLE dbo.Orders
       ADD CONSTRAINT FK_CartID_ProdID 
       FOREIGN KEY(CartID, ProdID)
       REFERENCES dbo.Cart(CartID, ProdID)



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 1, 2017 10:47 AM
  • create Table Orders(id int not null primary  key ,OrderID VARCHAR(50),CartID VARCHAR(50), ProdID VARCHAR(50))

    ALTER TABLE dbo.Orders
       ADD CONSTRAINT FK_CartID_ProdID 
       FOREIGN KEY(CartID, ProdID)
       REFERENCES dbo.Cart(CartID, ProdID)


    CREATE UNIQUE  INDEX isx  ON  Orders (OrderID,CartID,ProdID)

    INSERT INTO Orders VALUES (1,'OrderId-O001' ,'CartID-C001' ,'ProdID-P001')
    INSERT INTO Orders VALUES (2,'OrderId-O001' ,'CartID-C001' ,'ProdID-P002')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 1, 2017 12:40 PM
  • Hi,

    I think your structures are wrong.

    The Cart table can have CartId, ProductId, Qty, InitialPrice and Date columns.

    Then you need to have Orders table with CartId, OrderId, OrderDate, CustomerId

    and Order Details table that is child to the Orders table.

    That's the common scenario and will allow to link tables just fine.

    Also, there should be a separate Products table and normally you don't want to store product name in the OrderDetails or Cart tables unless your product names change often and you want to maintain original product name at the time of the purchase.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, March 1, 2017 12:52 PM
    Answerer
  • You must have CartID-C001 and ProdIDP002/P001 in Cart  table presented

    Think in that way, a child must have parents but parents musn't have children


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 2, 2017 6:19 AM
  • Here you go the whole script

    Create Table Product(ProdID VARCHAR(50) Primary key,Name VARCHAR(500))
    CREATE TABLE Cart (CartID VARCHAR(50) not null, ProdID VARCHAR(50)  not null foreign key references Product(ProdID))
    ALTER TABLE dbo.Cart ADD CONSTRAINT
    PK_Cart PRIMARY KEY CLUSTERED 
    (
    CartID,
    ProdID
    ) ON [PRIMARY]

    create Table Orders(id int not null primary  key ,OrderID VARCHAR(50),CartID VARCHAR(50), ProdID VARCHAR(50))

    ALTER TABLE dbo.Orders
       ADD CONSTRAINT FK_CartID_ProdID 
       FOREIGN KEY(CartID, ProdID)
       REFERENCES dbo.Cart(CartID, ProdID)

    CREATE UNIQUE  INDEX isx  ON  Orders (OrderID,CartID,ProdID)


    INSERT INTO Product VALUES ('ProdID-P001','First Product')
    INSERT INTO Product VALUES ('ProdID-P002','Second product')

    INSERT INTO Cart VALUES ('CartID-C001' ,'ProdID-P001')
    INSERT INTO Cart VALUES ('CartID-C001' ,'ProdID-P002')

    INSERT INTO Orders VALUES (1,'OrderId-O001' ,'CartID-C001' ,'ProdID-P001')
    INSERT INTO Orders VALUES (2,'OrderId-O001' ,'CartID-C001' ,'ProdID-P002')


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 2, 2017 6:40 AM

All replies

  • Hello Jyotsna,

      How do you store multiple product in Cart, in the same table ?

    if so that you should use cartid,productid as key to connect cart table from order table


    recommended way is to create order detail and cart detail table to store multiple items , so that so can use cartid alone in order table to refer the cart table

    please provide more info like sample input


    Thanks Saravana Kumar C


    • Edited by SaravanaC Tuesday, February 28, 2017 12:03 PM
    Tuesday, February 28, 2017 12:02 PM
  • At some point in time the cart will become an order.  You will need to add a new column to your order table as CartID and populate it with the cardid value.

    The way this is traditionally done is to have an order table which is a parent and then an order details table which is a child of your order table. It looks like your cart table is the child of the order table in this case.

    Tuesday, February 28, 2017 12:17 PM
  • Hi All,

    Thanks for the reply

    Please find the below details-

    Product-
    productID-P001
    ProductName-shoes
    Quantity-1
    Price-2000

    productID-P002
    ProductName-Bag
    Quantity-1
    Price-800

    so i need to order this two product using cart,
    1st i need to add this product in cart table

    cart-

    cartid-C001

    productID-P001
    ProductName-shoes
    Quantity-1
    Price-2000

    productID-P002
    ProductName-Bag
    Quantity-1
    Price-800

    after this i need to order these two item from cart when i click Place order
    button in my form

    one order should be created-

    Order

    OrderId-O001
    cartId-C001
    emailID-abc12@gmail.com

    Please Explain

    Thanks & Regards,

    Jyotsna


    Wednesday, March 1, 2017 4:23 AM
  • No, you need in Cart table have  at least 2 columns

    CREATE TABLE Cart (CartID VARCHAR(5), ProdID VARCHAR(5))-- Probably a PK constraint to enforce uniqueness 

    Then insert the data 

    INSERT INTO Cart (CartID ,ProdID ) VALUES ('C001','P001')

    INSERT INTO Cart (CartID ,ProdID ) VALUES ('C001','P002')

    Now we have an one cart with two orders, so same create table ORDERS with two columns (OrderID, CartID)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 1, 2017 6:21 AM
  • Hi

    Thanks for the  reply.

    but i am facing other issue on the time of creation Order table

    see the below query--

    Create Table Product(ProdID VARCHAR(5) Primary key,Name VARCHAR(5))

    CREATE TABLE Cart (CartID VARCHAR(5), ProdID VARCHAR(5) foreign key references Product(((ProdID)))

    i am getting error when i create order table

    Create Table Order(OrderID VARCHAR(5) Primary Key,CartID VARCHAR(5) foreign key references Cart((CartID))

    The column in table 'Cart' do not match an existing primary key or Unique constraint

    Please explain me this.

    Regards,

    Jyotsna



    Wednesday, March 1, 2017 9:37 AM
  • You need create PK constant on CartID

    CREATE TABLE Cart (CartID VARCHAR(5) NOT NULL PRIMARY KEY, ProdID VARCHAR(5) foreign key references Product(((ProdID)))


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 1, 2017 9:54 AM
  • Hi,

    It gives me error

    suppose in Product Table i have two data-

    ProductId-P001
    Name-shoes
    Price-200

    ProductID-P002
    Name-Bag
    Price-300

    i need to add these two product to cart table

    cartId-C001
    ProductId-P001
    Name-shoes
    Price-200

    CartId-C001
    ProductID-P002
    Name-Bag
    Price-300

    here the Cart id is same because i need to add both product in a single cart.
    when i use this query

    CREATE TABLE Cart (CartID VARCHAR(5), ProdID VARCHAR(5)foreign key references Product(ProdID))
    it working fine.
    after this i need order table to order this product

    Create Table Order(OrderID VARCHAR(5),CartID VARCHAR(5) foreign key references Cart(CartID))

    this statement is not allowed me to create Order table with cartid as foreign key because in cart table cart id is
    not a primary key.

    Error- Violation of PRIMARY KEY constraint 'PK__Cart__51BCD7971A14E395'. Cannot insert duplicate key in object 'dbo.Cart'.

    but when i change my cart table using your query

    CREATE TABLE Cart (CartID VARCHAR(5) NOT NULL PRIMARY KEY, ProdID VARCHAR(5) foreign key references Product(ProdID))

    it not allowed me to enter duplicate Data in cart table.

    please explain me.

    Regards,
    Jyotsna


    Wednesday, March 1, 2017 10:21 AM
  • Create Table Product(ProdID VARCHAR(5) Primary key,Name VARCHAR(5))

    CREATE TABLE Cart (CartID VARCHAR(5) not null, ProdID VARCHAR(5)  not null foreign key references Product(ProdID))
    ALTER TABLE dbo.Cart ADD CONSTRAINT
    PK_Cart PRIMARY KEY CLUSTERED 
    (
    CartID,
    ProdID
    ) ON [PRIMARY]


    Create Table Orders(OrderID VARCHAR(5) Primary Key,CartID VARCHAR(5), ProdID VARCHAR(5))

    ALTER TABLE dbo.Orders
       ADD CONSTRAINT FK_CartID_ProdID 
       FOREIGN KEY(CartID, ProdID)
       REFERENCES dbo.Cart(CartID, ProdID)



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 1, 2017 10:47 AM
  • Hi,

    can you please explain me how to insert data in orders table-

    Order table having 3 field

    OrderId-O001

    CartID-C001

    ProdID-P001

    ProdID-P002

    my question is here how to store data in  order table.

    Thanks & Regards,

    Jyotsna



    Wednesday, March 1, 2017 11:11 AM
  • create Table Orders(id int not null primary  key ,OrderID VARCHAR(50),CartID VARCHAR(50), ProdID VARCHAR(50))

    ALTER TABLE dbo.Orders
       ADD CONSTRAINT FK_CartID_ProdID 
       FOREIGN KEY(CartID, ProdID)
       REFERENCES dbo.Cart(CartID, ProdID)


    CREATE UNIQUE  INDEX isx  ON  Orders (OrderID,CartID,ProdID)

    INSERT INTO Orders VALUES (1,'OrderId-O001' ,'CartID-C001' ,'ProdID-P001')
    INSERT INTO Orders VALUES (2,'OrderId-O001' ,'CartID-C001' ,'ProdID-P002')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 1, 2017 12:40 PM
  • Hi,

    I think your structures are wrong.

    The Cart table can have CartId, ProductId, Qty, InitialPrice and Date columns.

    Then you need to have Orders table with CartId, OrderId, OrderDate, CustomerId

    and Order Details table that is child to the Orders table.

    That's the common scenario and will allow to link tables just fine.

    Also, there should be a separate Products table and normally you don't want to store product name in the OrderDetails or Cart tables unless your product names change often and you want to maintain original product name at the time of the purchase.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, March 1, 2017 12:52 PM
    Answerer
  • Hi 

    the given query is giving error-

    INSERT INTO Orders VALUES (1,'OrderId-O001' ,'CartID-C001' ,'ProdID-P001')
    INSERT INTO Orders VALUES (2,'OrderId-O001' ,'CartID-C001' ,'ProdID-P002')

    Column name or number of supplied values does not match table definition.

    Regards,

    Jyotsna






    Thursday, March 2, 2017 4:42 AM
  • Hi All,

    I have 4 table see the below table details

    Customer
    custID

    Product
    ProdID
    ProductName
    Quantity
    Price

    Cart
    CartID
    ProdID
    ProductName
    Quantity
    Price
    totalPrice

    Order
    CustID
    OrderID
    CartID
    totalPrice
    quantity

    i have 2 product and i need to order these two product using cart
    so see the sample data

    Customer
    custId-1

    Product-
    ProdID-P001
    ProductName-shoes
    Price-200
    Quantity-1

    ProdID-P002
    ProductName-Bag
    Price-300
    Quantity-1

    Cart
    CartID-C001
    ProdID-P001
    ProductName-shoes
    Quantity-1
    Price-200

    ProdID-P002
    ProductName-Bag
    Quantity-1
    Price-300
    totalPrice-500

    Order
    CustID-1
    OrderID-O001
    CartID-C001
    totalPrice-500
    quantity-2

    this my whole structure, please explain me how to achieve this.

    Regards,

    Jyotsna

    Thursday, March 2, 2017 4:46 AM
  • Jyotsna

    In my example a Order table has 4 columns but yours?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 2, 2017 5:46 AM
  • Hi

    According to  your example order table having 
    4 columns

    i try to create Order table in this  this way

    create Table Orders(id int not null primary  key ,OrderID VARCHAR(50),CartID VARCHAR(50), ProdID VARCHAR(50))

    ALTER TABLE dbo.Orders
       ADD CONSTRAINT FK_CartID_ProdID 
       FOREIGN KEY(CartID, ProdID)
       REFERENCES dbo.Cart(CartID, ProdID)

    CREATE UNIQUE  INDEX isx  ON  Orders (OrderID,CartID,ProdID)

    when i am going to insert value this way

    INSERT INTO Orders VALUES (1,'OrderId-O001' ,'CartID-C001' ,'ProdID-P001')
    INSERT INTO Orders VALUES (2,'OrderId-O001' ,'CartID-C001' ,'ProdID-P002')

    it give me error-

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CartID_ProdID". The conflict occurred in database "Test", table "dbo.Cart".

    The statement has been terminated.

    Please Explain me.

    Regards,
    Jyotsna

    Thursday, March 2, 2017 6:15 AM
  • You must have CartID-C001 and ProdIDP002/P001 in Cart  table presented

    Think in that way, a child must have parents but parents musn't have children


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 2, 2017 6:19 AM
  • Hi,

    i am not able to understand,

    can you Explain me.

    Regards,

    Jyotsna


    Thursday, March 2, 2017 6:29 AM
  • Here you go the whole script

    Create Table Product(ProdID VARCHAR(50) Primary key,Name VARCHAR(500))
    CREATE TABLE Cart (CartID VARCHAR(50) not null, ProdID VARCHAR(50)  not null foreign key references Product(ProdID))
    ALTER TABLE dbo.Cart ADD CONSTRAINT
    PK_Cart PRIMARY KEY CLUSTERED 
    (
    CartID,
    ProdID
    ) ON [PRIMARY]

    create Table Orders(id int not null primary  key ,OrderID VARCHAR(50),CartID VARCHAR(50), ProdID VARCHAR(50))

    ALTER TABLE dbo.Orders
       ADD CONSTRAINT FK_CartID_ProdID 
       FOREIGN KEY(CartID, ProdID)
       REFERENCES dbo.Cart(CartID, ProdID)

    CREATE UNIQUE  INDEX isx  ON  Orders (OrderID,CartID,ProdID)


    INSERT INTO Product VALUES ('ProdID-P001','First Product')
    INSERT INTO Product VALUES ('ProdID-P002','Second product')

    INSERT INTO Cart VALUES ('CartID-C001' ,'ProdID-P001')
    INSERT INTO Cart VALUES ('CartID-C001' ,'ProdID-P002')

    INSERT INTO Orders VALUES (1,'OrderId-O001' ,'CartID-C001' ,'ProdID-P001')
    INSERT INTO Orders VALUES (2,'OrderId-O001' ,'CartID-C001' ,'ProdID-P002')


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 2, 2017 6:40 AM