Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
I am doing TY project where there is a customer table and a paper table which store the names of the paper. I would like to store the paper order made by the customer in the order table.
How to do it? Please help out.
Posted 25-Jan-13 22:21pm
Edited 25-Jan-13 22:25pm
v3
Comments
André Kraak at 26-Jan-13 3:25am
   
Please DO NOT SHOUT when asking a question. When you use all capital letters it is seen as shouting on the Internet and considered rude. Next time when posting a question please use proper capitalization.
Member 9772922 at 26-Jan-13 3:28am
   
okk

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

If the customer can only have one paper, then that is quite easy - all you have to do is create a field in the Customer table which is athe same datatype as the ID for the Paper table rows, and put the ID for teh paper into the customer table.
 
So, if you have a Paper table
ID (int) PaperName (varchar(200))
1        Times
2        Mirror
3        Globe
And a Customer table
ID (int) CustomerName (varchar(200)) PaperID (int)
1        Joe                         2
2        Mike                        3
3        Dave                        2
Then that would indicate that Joe and Dave both take the "Mirror" paper, while Mike takes the "Globe". (You can also set the PaperID as a Foreign Key, and SQL will enforce that ID match)
 
If you want a customer to be able to take both the Mirror and the Globe, then you need to set up a third table instead which has the CustomerID and the PaperID:
ID (int) PaperName (varchar(200))
1        Times
2        Mirror
3        Globe
ID (int) CustomerName (varchar(200))
1        Joe                        
2        Mike                       
3        Dave                       
ID (int) CustID (int) PaperID (int)
1        1            2
2        2            3
3        3            2
4        3            3
  Permalink  
Comments
Member 9772922 at 27-Jan-13 1:27am
   
This solution is good but i thought of doing it but i dont know whether it is really good the thing is instead of creating a seperate i.e. third table with custid and paperid why cant we make the names of the paper as columns(
datatype bit ) and the columns will be set to 0 or 1 depending upon the paper selected by the customer
OriginalGriff at 27-Jan-13 3:48am
   
You could - but there are problems with that in the real world. Think about it: how do you add a new paper? Remove a paper because it is no longer available? How do you list "what papers does Joe Jones take"?
It gets surprisingly complex quite quickly, and difficult to maintain. It can also take more space in the DB because in the real world most people take one, maybe two papers - only libraries and TV/Radio news stations take a copy of each paper. (MsSQL optimises bits into bytes, but not all DBs will necessarily do that, so it can make the storage variable depending on which database you end up using)
 
And it gets difficult to process as well: SQL can't help you tie the names of the papers to the columns (and ensure what is called "referential integrity") unless you use the Row ID for each paper.
Member 9772922 at 27-Jan-13 9:14am
   
I have another table for paper where i have paperid,papername and the dealer id with whom i buy from.The idea behind to think this was that by doing this i thought of reducing the number of rows for eg:if all the client take 3 paper suppose and there are 1000 customers then it will take 3000 rows so i thought of just reducing it
OriginalGriff at 27-Jan-13 9:34am
   
I know what you mean, but the advantages generally outweigh the disadvantages.
Think about the tasks you need to do: order papers for example.
To do that, you need to look at how many of each paper you will sell. If you store that as a bit within each user record, that is a pain to do, because you want to return the number of papers where fieldx = 1 for each paper.
If you want to do it with a separate table, it's very simple (and exactly what SQL is good at)
SELECT paperId, COUNT(paperId) FROM CustomerTakesPaper GROUP BY paperId
returns you the paperId, and how many of it you need to order.
Member 9772922 at 28-Jan-13 1:00am
   
Im facing another problem as you said to create a third table with id,custid and paperid,how to retrieve the custid from the customer table in the third table
OriginalGriff at 28-Jan-13 6:24am
   
Sorry, I don't understand what you are asking?
Retrieve it when?
Member 9772922 at 29-Jan-13 7:59am
   
In customer interface i have a combobox from where the user selects the paper for a particular customer.Now how i will retrieve the paperid from the selected paper.I also have a listbox next to combobox to show the selected paper plz help me out.
OriginalGriff at 29-Jan-13 8:42am
   
Depends on what you are doing with it. Me, I would create a Paper class, which had an ID and a Name Properties, and which overrode the ToString method to return the name of the paper for the instance. I would then load the instance(s) into the Combo and List boxes. They automatically call the ToString method, so only the name is shown, but the ID is available to the code as well.
Member 9772922 at 29-Jan-13 8:07am
   
By the way i am using vs2010 and sql server r2 2008
Member 9772922 at 19-Feb-13 14:11pm
   
i am storing the retrieved customerid(1,2) into an array which inside a while loop and later when i try to retrieve the values from the array it is showing wrong values(2 and 0)
I am not able to understand what is happening?
and here is the code
string cust = "select distinct(customerid) from customerpaper";
SqlCommand custcmd = new SqlCommand(cust, con);
 
sda = custcmd.ExecuteReader();
 
if (sda.HasRows)
{
while (sda.Read())
{
i = 0;
customerid[i] = Convert.ToInt32(sda.GetValue(0).ToString());
 
listBox1.Items.Add(customerid[i]); //customerid 1 and 2 is stored
 

custid = customerid[i];
//textBox1.Text = custid.ToString();
 
// absent1(custid, customercoun);
// listBox1.Items.Add(customerid[i]);
i = i + 1;
 
}
}
sda.Close();
 
for (int j = 0; j < 10;j++ )
{
 
listBox2.Items.Add(customerid[j]);//customerid 2 and 0 is being retrieved
}
OriginalGriff at 19-Feb-13 15:16pm
   
Good grief! It's been three weeks, I can't remember that far back! :laugh:
Check your data. Always start by checking your data: look at what is coming back from the database each time (I asssume from your comments that listBox1 contains two items: 1 and 2), If so, and listBox2 contains different data then you need to look elsewhere - either your customerid array is being corrupted, or your listBox is.
 
BTW: This is not related to the existing question, so it should be posted as a new one in future!

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 335
1 OriginalGriff 250
2 DamithSL 170
3 Kornfeld Eliyahu Peter 130
4 Peter Leow 95
0 OriginalGriff 7,315
1 DamithSL 5,199
2 Sergey Alexandrovich Kryukov 4,917
3 Maciej Los 4,866
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 26 Jan 2013
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