how to create one to many relatinship in sql server

Posted by Shanky11 under Sql Server on 9/20/2013 | Points: 10 | Views : 2952 | Status : [Member] | Replies : 8

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

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

0	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

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

0	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

0	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

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

0	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

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

0	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

Latest Posts