how to create one to many relatinship in sql server

Posted by Shanky11 under Sql Server on 9/20/2013 | Points: 10 | Views : 1597 | Status : [Member] | Replies : 8
i have created a website for ecommerce
in that on add cart product is added to temporder table
now on final order
i created a table orderdetails
how can i create a relatinship b/w this 2 table

tempordertable column
pid ,pname, pimage price, ipaddress, orderid



orerdetails
orderdetailsid ,orderid, quantity ,name, total price

how to crate aone to many relationship in this table




Responses

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
By using PRIMARY KEY and FOEIGN KEY we can get One-toMany relationship....

Example:
CREATE TABLE TableA

(id int primary key,
name varchar(10)
)

CREATE TABLE TableB
( id int primary key,
TableA_Id int references TableA(id),
Column2 VARCHAR(50)
)

Here TableA to TableB relationship is One-to-Many



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shanky11 on: 9/20/2013 [Member] Starter | Points: 25

Up
0
Down
but the problem is
i am adding product to temporder table having column
pid, pname, pimage, ipaddress, orderid


orderdetails
odetailsid orderid,uantity, name, price,
but problem in temporder table from where i can get a unique orderid for same ipaddress beacuse thre are lots of product addde by a user to shopping cart in ecommerce website


Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
On which tables you wish to have one-to-many relationship?
tempordertable-to-orderdetails? or orderdetails-to-tempordertable ?

What is the common column between these two tables?

--May be this is what you are looking for?
tempordertable columns
pid ,pname, pimage price, ipaddress, orderid (Primary Key)

orerdetails
orderdetailsid ,orderid REFERENCES tempordertable(orderid) , quantity ,name, total price



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Actually the relationship among Product, Orders, OrderDetails should be as follows:

Products

pid PK , pname, pimage

Orders
orderid PK, ipaddress, Ordered_date

orderdetails
odetailsid IDENTITY PK, orderid FK (Orders),uantity, price, pid FK(Product)


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shanky11 on: 9/20/2013 [Member] Starter | Points: 25

Up
0
Down
orderdetails to temporder table

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
orderdetails to temporder table is one-to-many means

CREATE TABLE orerdetails (orderdetailsid INT PK ,orderid , quantity ,name, total price

CREATE TABLE tempordertable (pid ,pname, pimage price, ipaddress, orderdetailsid REFERENCES orderdetails(orderdetailsid ) )


I'm not sure about your DB design...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Notice that orderdetailsid column to tempordertable.... then you can refer orderdetailsid from orderdetails table and also indirectly you can store ipaddress based on orderid from orderdetails table...
CREATE TABLE tempordertable (pid ,pname, pimage price, ipaddress, orderdetailsid REFERENCES orderdetails(orderdetailsid ) )

If this is not working as expected... Tell us the exact scenario how you are using these tables and what is purpose clearly

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
i think you already using temporder and orderdetails tables..... click on "mark it as answer"

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response