Multiple Values For Single Row

Question

0.00/5 (No votes)

See more:

Multiple Values For Single Row

hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..

now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...

so far i created a table 'UserAccount' with column names as
UserName as varchar(50)
Password as varchar(50)
EmailID as varchar(100)
DateOfJoining as datetime
UserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..

so now every user must have a list of other userid's.. as contact list..

Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..
THANK YOU !

Posted 12-Feb-13 19:50pm

Raju Selvam

Add a Solution

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-02-12T21:19:00

There are two ways to do it - either your contact list is a string with contacts separated by a special character, or you have a separate table for this.
The problem is that SQL does not have a concept of arrays - so you can't have a row with a variable number of fields.
While you could set it up with a string:

UserID|UserID|UserID

THis very quickly becomes a PITA to manipulate or search, or worse - remove a user from your system completely.

The best approach is to create a separate table, "Contacts":

ID          int - This can be an identity field.
UserID      int - ID of your user
ContactID   int - ID of one of his contacts

You can then fetch the list of contacts with a simple SQL statement:

SQL

SELECT ContactID FROM Contacts WHERE UserID=100

Changing the 100 to match the ID of your user.