Click here to Skip to main content
15,907,395 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Hi I need add multiple sales orders for a single sale how to do it?

Actually suppose if a customer purchases four items on single bill then what is the table structure for it?
Posted
Updated 12-Sep-14 1:46am
v2
Comments
Magic Wonder 12-Sep-14 7:44am    
Not a good question. Not enough info to help you out.
Neetin_1809 12-Sep-14 8:28am    
Hello ,
Use Should uSe The Parent Child Relation SHip if you are the aware.
Kindly see the solution 2 which will give you the clear Id

Hi,


From your query below should be the table structure.
SQL
CREATE TABLE tbl_Purchase_Dtls
(
PD_ID BIGINT IDENTITY(1,1),
PD_CUST_ID BIGINT,
PD_ITEM_ID INT,
PD_ITEM_QTY INT,
PD_ITEM_AMT DECIMAL(10,2),
PD_BILL_NO VARCHAR(50),
PD_CREATED_DT DATETIME DEFAULT GETDATE(),
PD_CREATED_BY VARCHAR(100),
PD_UPDATED_DT DATETIME,
PD_UPDATED_BY VARCHAR(100),
PD_STATUS_FLAG INT
)


Hope this will help you.


Cheers
 
Share this answer
 
According to your explanation, suppose you have two tables in your sql database called Transaction and TransactionItemWise. Transaction table stores any transaction as a summary and TransactionItemWise table keeps them itemwise. That means there is 1:M relationship between Transaction and TransactionItemWise tabels. So as we know 1 side key goes to M side. See the example below

SQL
CREATE TABLE Transaction(
TrxID varchar(3),
TotAmount float,
TotQty int,
TrxDate DateTime,
CONSTRAINT pk_Transaction PRIMARY KEY (TrxID)
)


SQL
CREATE TABLE TransactionItemWise(
TrxID varchar(3),
ItemNo varchar(3),
ItemAmount float,
ItemQty int,
CONSTRAINT pk_TransactionItemWise PRIMARY KEY (TrxID,ItemNo),
CONSTRAINT fk_PerOrders FOREIGN KEY (TrxID)
REFERENCES Transaction(TrxID)
)
 
Share this answer
 
Please see following structure.

You Can have multiple orders (Order Header)for the customer
Multiple Order Lines(different items) for One Order
Further you should have product table

SalesOrders[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900