I have another question about circular references. I will use simplified example to illustrate my question.
Lets say I have the User table and the Comments table.
I have 2 use cases:
1- I want to get all the comments posted by user xyz
2- I want to get all the comments posted along with their Users (ie all the comments posted today and their user name)
I understand that I can achieve one of them by having a reference from one table to another.
Is it bad (or frawn upon) to have 2 ways relationship? I mean User table would have a column referencing the Comments table. And the Comments table would have a column referencing the user Table.
Theoritically, this way it should allow me to load any row with its relation.
Please let me know if you see any issues with this
Thanks for your reply
This is not what I meant.
What I meant is if you table A and table B.
Then A column 1 will have a relation to table B.
And B column1 will have a relation to table A
Is this bad idea?
So if we look at the comments example above
I want to retrieve user xyz and all his comments.
I also want to retrieve all comments with their userId
It’s possible to have circular reference between two tables but is redundant in your example with Users and Comments.
Having just user column in Comments table gives you an ability to retrieve both kind on information.
To retrieve user xyz comments, use the following where clause for Comments table: user.objectId = ‘XYZ_USER_OBJECT_ID’
To retrieve all comments with their userId, you don’t need where clause at all. But in this request you have to specify an option to retrieve user relation (https://backendless.com/docs/rest/doc.html#relations-retrieve)
I don’t think it is redundant. What you explained makes sense if you have one comment and you want to know the user for it.
The problem happens when you want to load all the comments for the day (could be 100) along with their users. You don’t want to load the user for each comment one by one as that would be so. Many calls and so slow.
This is why I suggested the circular reference. I just wanto to make sure that I don’t end up with infinite loop when querying :
User is linked to comments and the comments are linked to user who is linked to comments… Etc
if you want to load lets say 100 Comments and know about publisher (user), you just need one of this
and you will have all the comments along with users in a single API Call (see my the last screenshot above)
circular relations works well so you can try do it, and also you can try with one way relations =)
Correct. That would work if I want to load 100 comments along with their publisher. In the same APP in a different use case, I want to load all the comments belonging to single user (ie ,please show me all my comments that I posted).
This is why I suggested the cuircular relation as I can go either direction
query user and all his comments
query all comments along with their user info
Cool, as long as this is “not bad” way of doing things then it solves my problem