Database table in SQL Server 2008

Question

1.00/5 (1 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 21:21pm

Member 9772922

Updated 25-Jan-13 21:25pm

André Kraak

v3

Add a Solution

Comments

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

okk

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2013-01-25T21:53:00

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

Posted 25-Jan-13 21:53pm

OriginalGriff

Comments

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;

}
}
sda.Close();

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!