Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jashwant
Starting Member
2 Posts |
 Posted - 2011-03-12 : 00:23:50
|
| Hello friends , I am newbie here.In my project a user can have multiple friends and I need to store friends of a each user. What I am doing is that , in my table , I have created two columns ; one for username and second for friends.When a user adds a new friend name 'newfriend', I update like this :"update table set friend=friend+'newfriend|' where user=myuser"Then, when I need to select different friends, I fetch friend column and split the value by '|' to get an array of friends.This code is working fine. But I know, you geniuses have something better in mind. |
|
|
subhbwn
Starting Member
6 Posts |
Posted - 2011-03-12 : 01:50:08
|
| Instead of updating, you can insert a new record for each user and his friend. That will make it easy to fetch data later on. You can see various INSERT related commands here http://www.w3resource.com/sql/insert-statement/sql-insert-command.php. |
 |
|
|
jashwant
Starting Member
2 Posts |
Posted - 2011-03-12 : 02:35:13
|
| But , it will create a lot of multiple entries for single user |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-03-12 : 04:11:50
|
| See: [url]http://www.simple-talk.com/sql/learn-sql-server/facts-and-fallacies-about-first-normal-form/[/url]MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-12 : 07:42:26
|
Jashwant, to add to suggestions from mmarovic and subhwbn, think about the difficulties that you will run into with your current design. For example,How will you unfriend someone?or, Assuming friendship is not a one-way thing, in your example, how will you indicate that for user=newfriend, myuser is a friend?or,Given two names, how will you find people who are friends with both?etc. etc.At the very least, make two tables. One for "Users" - which have basic data about users and with a unique user id as the primary key. Then a second table for "Friends" with two columns. When two people want to be friends insert their user ids into that table. From there, you can query for anything you want. You can even enhance it for other things - for example add another column to indicate when they became friends, when they got married to each other, when the unfriended etc. etc. |
|
|
|
|
|
|
|
|
Correspond multiple values to a single user