none
Add Multiple Product From Product Table In To A Single Row Of Sale Table RRS feed

  • Question

  • Hi ,

    I Work On Food Order System ,For Ordering process , I think We need tow table , A table that stores information about the Foods "Food ID , Food Name , Price" , And A Table , Named "Sale Table" that stores the Information of foods that sold . If Costumer buy just one food , It is OK . But If Buy Several Food , How i can Store the information in a table in single row ?
    a row of Sale table , is contained of "Sale ID , Product ID , Costumer ID, ... " !

    Tuesday, November 27, 2012 11:47 AM

Answers

  • You can make it a delimited string list. See example 4 in the following blog:

    http://www.sqlusa.com/bestpractices/training/scripts/commadelimitedlist/

    Delimited strings do not comply with 3NF rules since in 1 cell more than 1 value stored.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, November 27, 2012 2:14 PM
    Moderator
  • Hi H.S.Shamloo,

    I suggest one Sale Table row store one food that the customer purchases, for example:

    create table Customer
    (
    	ID int primary key,
    	Name varchar(50)
    )
    
    Create table Food
    (
    	FoodID int primary key,
    	FoodName varchar(100),
    	Price Decimal (19,4)
    )
    
    create table Sales
    (
    	ID int identity (1,1) primary key,
    	CustomerID int foreign key references Customer(ID),
    	FooDID int foreign key references Food(FoodID),
    	Numbers int,
    	SaleDate datetime default getdate()
    )
    
    insert into Customer values (1,'A');
    insert into Customer values (2,'B');
    
    insert into Food values (1,'Food A',10.00);
    insert into Food values (2,'Food B',15.35);
    insert into Food values (3,'Food C',19.35);
    
    -- Customer A buy one Food A and one Food B
    insert into Sales (CustomerID,FooDID,Numbers) values (1,1,1);
    insert into Sales (CustomerID,FooDID,Numbers) values (1,2,1);
    
    -- Customer B buy one Food A, two Food B, and one Food C
    insert into Sales (CustomerID,FooDID,Numbers) values (2,1,1);
    insert into Sales (CustomerID,FooDID,Numbers) values (2,2,2);
    insert into Sales (CustomerID,FooDID,Numbers) values (2,3,1);
    


    Allen Li
    TechNet Community Support

    Wednesday, November 28, 2012 8:49 AM
    Moderator

All replies

  • You can make it a delimited string list. See example 4 in the following blog:

    http://www.sqlusa.com/bestpractices/training/scripts/commadelimitedlist/

    Delimited strings do not comply with 3NF rules since in 1 cell more than 1 value stored.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, November 27, 2012 2:14 PM
    Moderator
  • You don't.  That is a bad and anti-relational design.  You can review the AdventureWorks schema to see how it handles sales of multiple items - it uses a very typical structure of header/detail tables.
    Tuesday, November 27, 2012 2:24 PM
  • Hi H.S.Shamloo,

    I suggest one Sale Table row store one food that the customer purchases, for example:

    create table Customer
    (
    	ID int primary key,
    	Name varchar(50)
    )
    
    Create table Food
    (
    	FoodID int primary key,
    	FoodName varchar(100),
    	Price Decimal (19,4)
    )
    
    create table Sales
    (
    	ID int identity (1,1) primary key,
    	CustomerID int foreign key references Customer(ID),
    	FooDID int foreign key references Food(FoodID),
    	Numbers int,
    	SaleDate datetime default getdate()
    )
    
    insert into Customer values (1,'A');
    insert into Customer values (2,'B');
    
    insert into Food values (1,'Food A',10.00);
    insert into Food values (2,'Food B',15.35);
    insert into Food values (3,'Food C',19.35);
    
    -- Customer A buy one Food A and one Food B
    insert into Sales (CustomerID,FooDID,Numbers) values (1,1,1);
    insert into Sales (CustomerID,FooDID,Numbers) values (1,2,1);
    
    -- Customer B buy one Food A, two Food B, and one Food C
    insert into Sales (CustomerID,FooDID,Numbers) values (2,1,1);
    insert into Sales (CustomerID,FooDID,Numbers) values (2,2,2);
    insert into Sales (CustomerID,FooDID,Numbers) values (2,3,1);
    


    Allen Li
    TechNet Community Support

    Wednesday, November 28, 2012 8:49 AM
    Moderator