Data Architecture for Social Network - Would Love Input

Josh Chambers

I can schedule a virtual office meeting if preferable, but I think this would be an interesting thing to talk through with others... I'm wondering how Xano would suggest building some basic "friending" interactions.

Current Databases
1. Users (Authenticated)
2. Posts1. All posts have a "sender" and "recipient." I will be the sender, and the default recipient is myself.


Functionality
1. User (sender) can request to be friends (recipient) of another existing user. 
2. Recipient can approve or deny request.
3. If approved, it is a mutual friendship. Status = friends. There are no variations on friend status type (it's an on/off switch).
4. Once friends, users can send posts to each other. Kind of like a wall back in the day, or a DM (minus the realtime).
5. In a feed, users can view all posts sent to them (including those sent from self, to self). I should add that these will be authenticated requests and there is no concept of a "public" feed. 
6. Users can block friends. Status = blocked. Blocked users cannot request friendship again.
7. Users can have multiple friends. 


There are a lot of UX implications here. I've built a few of these products in the past, but I'm wondering the most efficient architecture using Xano. I considered adding a "Friends" table and I considering using Join Inner. 

Thanks in advance!

Ray Deck

That sounds like a relationship table. Where you have A, B, and type of relationship. 

So A can block B. That leads to A, B, "blocked". 

If B wants to friend A, the system would first check whether A has a block on B. Seeing that record, can go to the "else". 

Or A can friend B. This should generate two records: A, B, "friend" and B, A, "friend". That will reduce your calls to the db and help performance. So you could straightforwardly inner join relationship to posts to get the posts associated with all my friends.  Any delete of that relationship (assuming all relationships are mutual) would delete both records. 

Anyhow, that's how I would do it. Others might have different opinions!

Josh Chambers

Thanks, Ray.

Any other ideas or opinions?

It's interesting to consider because so many of the current social networks use a "follower" based model with privacy off by default. The "Friending" paradigm, which requires approval, isn't used as frequently anymore. So, most of the DB models you can find online suggest a SQL-esque "join" table.

My agency built a 250+ screen mobile app social network a few years back, and it had a lot of these complex relational statuses (pending, blocked, admins, super admins, etc.). I'm going to talk to our engineers about what model they used - I think we were running Mongo amongst other things. It's been awhile...and this is my first foray into actually doing the back end engineering (I'm having so much fun). 

Anyway, other ideas of how to use Xano for this model? I'm intrigued by its ability to be Graph-esque. 🤓

Thanks!

Ray Deck

For a follower model at scale? User (user, profile, auth), post (author, content), follow (follower, followed), likes (post, liker, clap count (if medium style)). You'll introduce secondary unique indices to manage performance for reverse lookups as you grow, but that's not necessary to start.

The graph-y part would involve self-joins. E.g. how about who also liked a post that you liked? Who do people who liked the same posts as you did follow? These behavioral and collaborative filtering opportunities just involve connecting tables back onto themselves. If you give yourself a loose, relationship-driven structure like the above it becomes relatively straightfoward to implement those more advanced social graph features.

Database design is one of the hard parts about advanced app development. But it permits a great deal of power. Let me know if you want to discuss your particular needs 1-1. 

Josh Chambers

 Thanks. That's great to know about the 1-1. I appreciate all the help so far as well.