Click here to Skip to main content
13,356,787 members (83,680 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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
Updated 25-Jan-13 22:25pm
André Kraak 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 26-Jan-13 3:28am

1 solution

Rate this: bad
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
Member 9772922 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 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 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 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 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 28-Jan-13 6:24am
Sorry, I don't understand what you are asking?
Retrieve it when?
Member 9772922 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 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 29-Jan-13 8:07am
By the way i am using vs2010 and sql server r2 2008
Member 9772922 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;


for (int j = 0; j < 10;j++ )

listBox2.Items.Add(customerid[j]);//customerid 2 and 0 is being retrieved
OriginalGriff 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
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.180111.1 | Last Updated 26 Jan 2013
Copyright © CodeProject, 1999-2018
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