0

I am designing an application where an account holder has "friends". How would I go about linking those friends to the original account holder? Would an efficient SQL table be something like:

AccountHolderID (varChar 20)
FriendsID (nText)
user279521
  • 4,779
  • 21
  • 78
  • 109
  • Is a Friend another user/account holder in your system? – Scott Saunders Jul 09 '10 at 15:59
  • yes, you are correct. I am thinking a FriendID would have to be a foreignKey (so it can't be nText), perhaps? – user279521 Jul 09 '10 at 16:01
  • 1
    If it is assumed that each account holder can have one or more friends, and each friend can be associated with one or more account holders, then you need a many-to-many relationship. See my answer to [this question](http://stackoverflow.com/questions/3192416/will-this-normalised-database-structure-permit-me-to-search-by-tags-as-i-intend/3192607#3192607) for an example of a many-to-many relationship in MySQL. Replace items with account holders, and tags with friends, and you have the basis for your schema. The web is full of tutorials on database relations. – Mike Jul 09 '10 at 16:10
  • Was your intention with making FriendsID an NTEXT field to store a string along the lines of "personid1, personid2, personid3"? If so, you should understand that this would make queries involving this field very difficult to write and highly inefficient - you should avoid storing multiple values in a single field - it's not the database way! – Will A Jul 10 '10 at 07:17
  • yes, the intention was to say UserID1001 has a friend UserID2001, UserID3021, UserID3310 etc. – user279521 Jul 10 '10 at 18:15

1 Answers1

0

We implement something similar--using a table to hold the friends... Ours breaks down quite simply as a table of friends...

AccountHolderId( type)
FriendAccountHolderId ( type)
Created(datetime)  // useful for tracking when the friend was added

Both of the Id fields link back to the Accounts/Users table.

reallyJim
  • 1,336
  • 2
  • 16
  • 32