Designing a Buddy / Friend List database

Posted on 2006-05-13
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

    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

    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 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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?
    LVL 4

    Author Comment

    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 76

    Expert Comment

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

    Author Comment

    Works for me.  Thanks guys.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    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…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now