Click here to Skip to main content
15,125,228 members
Please Sign up or sign in to vote.
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 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 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..

1 solution

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:
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:
SELECT ContactID FROM Contacts WHERE UserID=100
Changing the 100 to match the ID of your user.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900