Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Designing a Buddy / Friend List database

Posted on 2006-05-13
Medium Priority
Last Modified: 2009-12-16
I'm designing a social networking site in the vein of a specialized myspace.  The two main features of it are a buddy list and a favorite thing list.

What is the standard table architecture and relations for things like this?  I can only think of a couple possibilities:

1) Each user has their own buddy list table and favorite thing table, so the layout becomes:

UserTable (UserID, name)
UserXBuddies(UserID)  /* where X is the name of a user; there will be 1 for each user */
FavThings(ThingID, thingname)
UserXFavThings(ThingID) /* same as above */

this seems pretty nonstandard but also a good way to avoid db file fragmenting and keep queries fast as (hopefully) hundreds of users flock to the site.

2) Giant tables with a lot of redundancy

UserTable (UserID, name)
UserBuddies(UniquePKInteger, UserID, buddyID)
FavThings(ThingID, thingname)
UserThings(UniquePKInteger, UserID, ThingID)

Which seems like the quick and dirty way, but it irks me to thing of 100 rows where UserID is the same.

I know plenty about query syntax and am no stranger to DBs, but I'd say my skills at creating efficient architectures are average at best, so I would appreciate advice on what approach to use.
Question by:noamattd
LVL 65

Assisted Solution

rockiroads earned 300 total points
ID: 16677027
Your buddies, are they registered users?

I'd say your 2) approach is the best way, and I believe the proper way

Having the unique id in UserBuddies is optional because u can make a composite key between UserID and BuddyID

You may have lots of rows, but in terms of maintenance, query design etc, this will lead to a easier life
with UserBuddies, u can automatically create the reverse relationship

i.e. userB is buddy of userA

so userA logs in, u query UserBuddies and it returns userB

u then do not need to create the reverse relationship
when userB logs in, u check his buddies, but then u and see who he is a buddy of by looking at BuddyId to UserID

LVL 42

Accepted Solution

dqmq earned 1200 total points
ID: 16677044
Option 1 is horrible.  It means creating two new tables each time a user registers.  It means giving ordinary users DBA authority.  It means next-to-impossible queries.  For example, how could you give Joe a list of all the buddy lists he belongs to?  For that matter, it precludes static SQL any time the UserBuddies or UserThing table is queried.   It's unmanageble; d          on't even think about going there.

Option 2 is immensely better. However, I'd probably make a composite PK out of the two ID columns and forgo the UniquePKInteger thing.  

Your concern about redundancy is misguided.  Redundancy is when you store the same fact more than once.  Having the same UserID appear 100 times, each with a different buddy is not redundant.  That's a valid relationship that you want to store and retrieve.  It's much better to record that relationship in your data than to try and record it in your schema structure.  

You can address performance with proper indexing and partitioning, however, I doubt "hundreds of flocking users" would even blip the radar scope.

LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16684139
I agree with both comments thus far:  a composite primary key is the correct way to go.

The only thing I haven't seen addresses yet is the 'userThings' table.  If you never expect to have more than 1 favorite thing then you might look at adding this to the user table.

Are you looking to have a canned set of favorite things or can the user 'add' their own?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 16687043
The user can add as many favorite things as they want from a preset list of things.  The one giant table be the way to go for that as well, I assume?
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16690123
Then I go with the crowd:  Option 2 with composite keys on UserBuddies and UserThings .

Author Comment

ID: 16695425
Works for me.  Thanks guys.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question