Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET
I am making a app in which one table is for storing client information and other is the for products I have. There is no limit of products as any new product will be added on it.

Each time I add a client I give options to select any product from all list of products by selecting checkbox of each product.After all this process user will click submit button. I want to store the Id of each product selected and store it into database with customer Id.But I am very confused in database design.Please Help...
 
[edit]Spurious code block removed - OriginalGriff[/edit]
Posted 25-Nov-12 23:20pm
Edited 26-Nov-12 1:01am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Sounds like you need to have three tables, not two.
Customer table:
CID, Name, Address, etc.
Product table:
PID, Name, Description, Price, Image, etc.
CustomerOrderDetail:
OID, CID, PID, Date, etc.
It may be that you want a fourth table to handle individual orders from a customer: Ones that get delivered together, invoiced toghether, paid together, and so forth.
In which case you would replace the third table with two.
CustomerOrder:
COID, CID, Date, status, etc.
CustomerOrderDetail:
CODID, COID, PID, etc.
The later setup is more normal when you might have a customer making an order each week, say.
  Permalink  
Comments
jaswinder Singh03 at 26-Nov-12 5:01am
   
I am satisfy with your answer Sir , but I want to confirm that if customer will order 10 products then is there 10 records will submitted to CustomerOrderDetail Table with same cust_id and different pr_id's......Thanks for answering my question
OriginalGriff at 26-Nov-12 5:18am
   
Yes - that is the idea exactly.
Or, if you keep it as they do for invoices, then one CustomerOrder row per invoice (with a suctomer id), and a number of CustomerOrderDetial rows with one COID and a product ID each.
jaswinder Singh03 at 26-Nov-12 5:20am
   
Thanks Sir
shaikh-adil at 12-Dec-12 0:06am
   
+5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
You can maintain 2 tables in your database namely, TblProduct and TblClient.
 
for ex:
create table TblProduct(
ProductID int primary key not null,
ProductName varchar(50) not null,
ProductDetails varchar(100) not null)
and
create table TblClient(
ClientId int primary key not null,
ClientName varchar(50) not null,
ClientDetails varchar(100) not null,
ProductID1 int not null)
 
Later you can use a query to retrieve what all details you need and display it as your end result.
select * from TblProduct
will yield:
ProductID ProductName ProductDetails 
1	BMW	vehicles
2	Lenovo 	PC
3	HP	PC
4	Kingston Peripherals
select * from TblClient
will yield:
ClientId ClientName ClientDetails ProductID1 
100	YY	BeachSide	1
101	XX	RiverSide	2
102	ZZ	CountrySide	5
103	AA	No Details	4
105	BB	TownHall	6
 
Then you might use a query like:
select TblClient.* from TblClient  join TblProduct on TblProduct.ProductID=TblClient.ProductID1
The answer based upon the above entries will yield:
ClientId ClientName ClientDetails ProductID1  
100	YY	BeachSide	1
101	XX	RiverSide	2
103	AA	No Details	4
 
It gives the details of the clients who have purchased the product which is present in the product list.You can store it in a separate table if you want.
Hope it helps
  Permalink  
Comments
jaswinder Singh03 at 26-Nov-12 5:16am
   
Hi Anurag , Your solution is only for one to one relation means if customer buys more than one product it will not prove useful.
anurag3487 at 26-Nov-12 5:22am
   
Yeah thx for pointing..In that case you can have a 3rd table having fewer more details such as orderid,clientid,productid etc...and then you cn retrieve using joins wat all you need.. :)
shaikh-adil at 12-Dec-12 0:06am
   
+5

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



Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 26 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100