Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    20

    Unanswered: How to store something like Facebook Groups in DB?

    On Facebook, you can join/become a fan of groups. How do they keep track of things like that?
    For my use, I'm looking at having users become a fan of different things.
    Would you store it like this in the database:
    Table: Fans
    Columns: User_ID || Fan_Of (ID of group is stored here) || ID (Primary key? can't see ever using this field for any output...)
    Row: 1 || 3 || 1
    Row: 1 || 5 || 2
    Row: 1 || 4 || 3
    Row: 3 || 9 || 4

    Where each time a user becomes a fan of something, they get a a new row in the table.

    Is there a more efficient way of doing this? Thanks in advance!

    P.S.
    In the same topic (I think), would you store comments on a article the same way?
    Table: Comments
    Columns: User_ID || Article_ID || Comment || ID (Primary key? can't see ever using this field for any output...)
    Row: 1 || 3 || "Great article!" || 1
    Row: 1 || 5 || "Good job!" ||2
    Row: 1 || 4 || "Impressive!" || 3
    Row: 3 || 9 || "Good job!" || 4

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tlshaheen View Post
    On Facebook, you can join/become a fan of groups. How do they keep track of things like that?
    no one really knows, but here's one idea...

    Facebook database schema on Flickr - Photo Sharing!

    by the way, you're right, the auto_increment column in your tables is useless
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    20
    Thank you for your reply, nice schema.

    I wasn't specifically needing to know what Facebook does - it was just an example of what I'm looking for. Databases are created all the time for comments on articles, or for users "favoriting" things.
    Any insight on what would be the best schema for it?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tlshaheen View Post
    Any insight on what would be the best schema for it?
    you've pretty much got the gist of it already in the tables you've suggested

    for the fans table, the PK should be the composite (User_ID,Fan_Of), since you want uniqueness (a user cannot be a fan of the same thing more than once)

    for the comments table, the PK would need to include a datetime, as you probably would want a user to be able to comment more than once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    20
    Thank you very much, I forgot about composite keys (duh!).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •