Best schema for "following" user relations

Data Service
1

I have a question about the tables, relations, Backendless database and schema/architecture:

We have users who can follow other users, lets say users are in “Table A”. When a user follows another user, they also have some preferences about that relationship, so we need a “Table B” which has more detailed information about the relationship.
The question is, what’s the best schema?

A) One schema that does this is fairly simple: any following relationship is put into Table B and we create two 1:1 relations in Table B: “following” and “is followed by”.
However, if I want to show user X all of their followers, I have to find all records in Table B which point to Table A where the user_id = X… and then retrieve all user_id records in Table A that that relate to those records in Table B via the “following” relation. If we’re talking about 1000 followers, this might be a slow query. Or maybe it’s fine?

B) Another schema could be to create a 1:MANY relation from Table A to itself, and also a 1:1 relation from Table B to Table A. So it would be very simple to just get all followers of user X.
But if we also want to get the details about the following relationship(s), it would mean again going to Table B, finding all records where the user being followed is user X (by looking at the “is followed by” relation) and getting the details back in a list. And then we would have to match the records in that list to the list of followers that we got from our first query (get followers for user X in Table A)… maybe not the greatest solution.
On the other hand, if the relationship details are only shown for individual followers to user X (when the user wants to investigate a bit more about a particular follower), maybe that’s fine?

C) Perhaps a 1:MANY relation from Table A to Table B (“following”), and another 1:MANY from Table A to Table B (“is followed by”) is the best? Then we can easily show user X who their followers are, or we can easily show them who they are following themselves…

Or perhaps there is another schema that would be even better?

2

Hi, @Alex_Klein

I think you have to try and see which one is best for you. You will also be able to see some disadvantages that may not even be obvious at the moment. But, from the options you suggested, option C seems to me the easiest and most effective for your case.

Regards,
Marina

3

Hi Marina, ok thnx — another related question: is it always a bad idea to duplicate some data across two tables? For example, “name” in user table and also have “name” in some other table… are there some circumstances where it makes sense to do that or is it always bad practice in Backendless? I guess I’m talking about “denormalization”

4

@Alex_Klein, in most cases it is better not to duplicate and make connections. But in cases when you have a lot of data and you want to quickly get them or filter them, it is better to duplicate.

Regards,
Marina

1 Like