Facebook Wall (sort of)

sleepydad

I'm creating a site where I want to have a page for each member somewhat like a Facebook wall, but I doubt I'll be as complex with all the LIKE's and emojis and stuff. Basically it'll be a conversation area where someone can post a story with optional photo and people can leave comments and these comments can be replied to and maybe upload a photo to include with a comment. I've been going back and forth with how to build the MySQL database trying to look into the future where there could potentially be a large pool of members. Initially I was going to have a table for the topic itself with another for comments and either in that table a column for replies or another table with the replies. I could make all that work, but got to thinking how intensive it would be calling in all of this stuff and assembling it if I had a large member base. So ... I started toying with saving each topic, including comments and replies and photos, as its own array that I would serialize and save as a large blob, unserialize to update and serialize and re-save. I have a working model using arrays, and it works wonderfully in a local testing environment.

I'd say on a scale of 1-10 my knowledge of MySQL is probably around a 4, so be gentle with your answers. I'm learning as I go, and am asking here if my solution using arrays is impractical or maybe I'm a genius and I've discovered the Holy Grail. Or should I go the multiple tables route as I was inclined to do at the onset? Logically, to me at least, it seems that saving each as its own little cocoon that opens and closes as needed would be much more efficient than joining this table to that table to the other table to another table sorting and assigning the values to their respective divs in the display.

Opinions?

NogDog

First thing that comes to my mind is deciding whether or not replies to comments are required, and if so, can they be nested? (For instance, on FaceBook, you can reply to a comment, but you can't reply to a reply to a comment (yeah, you can click a thing that says that's what you're doing, but it really just adds a reply to the same comment with @whoever automatically inserted). If you want multiple levels of reply nesting, it gets complicated.

article
  comment
    reply
    reply
      reply to reply
        reply to reply to reply <-- how far can we go?
    reply
      reply to reply
  comment

sleepydad

[ATTACH]5341[/ATTACH]

This is a screen grab of the behavior so far. There is an intro with text ("Come and listen to my story ...") and optional photo (Beverly Hillbillies). Below that a visitor can leave a general comment with each comment having its own dropdown to reply to the comment resulting in the array:

[5] => Array ( [name] => Don V. [photo] => images/profilephotos/me.jpg [date] => [comment] => 924 [replies] => Array ( [0] => Array ( [name] => Don V. [reply] => a reply to 924 ) [1] => Array ( [name] => Don V. [reply] => Another reply to 924 ) )

Each reply to a comment is saved in the [replies] array as an array([name] and [reply]). (The photo is populated with a session variable established at log in, so is not necessary in the array)

It should be noted that each comment (dd,ff,sdfsf,etc) has its own dropdown to reply and the reply will display under the comment as illustrated on the very last "924".

NogDog;11055883 wrote:
First thing that comes to my mind is deciding whether or not replies to comments are required, and if so, can they be nested? (For instance, on FaceBook, you can reply to a comment, but you can't reply to a reply to a comment (yeah, you can click a thing that says that's what you're doing, but it really just adds a reply to the same comment with @whoever automatically inserted). If you want multiple levels of reply nesting, it gets complicated.
article
  comment
    reply
    reply
      reply to reply
        reply to reply to reply <-- how far can we go?
    reply
      reply to reply
  comment

Screen Shot 2016-06-06 at 9.09.15 AM.jpg

pbismad

got to thinking how intensive it would be calling in all of this stuff and assembling it if I had a large member base

The R in RDBMS stands for Relational. The mysql database engine is designed to efficiently optimize JOINed queries between related tables, assuming you have proper foreign keys and indexes set up.

By storing data, either serialized or as json, you will not be able to directly, in an sql query, search for any particular information, either by keywords or by member/user, count or sum any particular information, or do any other manipulation of the information, such as hide/delete all posts by a member posting spam.

To do any of these things with serialized or json data will require that you retrieve all the data, unserialize/json_decode it, then do any search, count, or manipulation of the data. As the number of members/users increases, this will require more intensive processing, using php code, with is a parsed, tokenized, interpreted language, that will typically take 5-10 times longer, times the number of rows being examined, than if you can do the same operation in an sql query, which uses complied code to perform these operations on the data.

sleepydad

Excellent points, pbismad. I hadn't thought about the necessity to search. Definitely something to consider.

dalecosp

NogDog;11055883 wrote:
First thing that comes to my mind is deciding whether or not replies to comments are required, and if so, can they be nested? (For instance, on FaceBook, you can reply to a comment, but you can't reply to a reply to a comment (yeah, you can click a thing that says that's what you're doing, but it really just adds a reply to the same comment with @whoever automatically inserted). If you want multiple levels of reply nesting, it gets complicated.
article
  comment
    reply
    reply
      reply to reply
        reply to reply to reply <-- how far can we go?
    reply
      reply to reply
  comment

However, people like ./ and reddit seem to have managed. I wonder if Facebook did it because of computational load/scale issues?

NogDog

dalecosp;11055897 wrote:
However, people like ./ and reddit seem to have managed. I wonder if Facebook did it because of computational load/scale issues?

Or they just didn't want to have to deal with it visually? 🙂

dalecosp

NogDog;11055899 wrote:
Or they just didn't want to have to deal with it visually? 🙂

Yeah, a company as big as FB probably has at least ONE of those types ... ;-)

Weedpacket

          I dunno,
          I do think
          that the
          nested
          thread view
          can get very
          annoying
          when the
          nesting is
          too deep.