I come from mysql background and I am adjusting to the service provided so I need a bit of help doing the right design.
Let’s say I have Users table ( system created table) and Contents table. A user can publish one or more content.
Option A: Does it make sense to have a an owner column in the Contents table and do 1 to 1 relation from Contents.owner to Users table?
Option B: Or do I add a content column to the user table and have a one to many relationship from User.content to Contents table.
Essential in the app I need to do 2 queries:
1- Give me all contents that a user has published (given user identity Or object ID)
2- Give me the owner info (user) for a specific content.
PS: I am thinking that option A makes the most sense in case I add more things like (User buy products, user went to addresses …etc). I would end up with many columns in the user table if I go with Option B while option A would be the neatest. At least this is how SQL DB might do it. But I need your input so I avoid future pitfalls
Given the requirements (1 and 2), I would recommend creating a one-to-many relation from the Users table to the Comments table. This way you will be able to execute both types of queries.
Thank you for your answer. If that model is followed then how can you get the user given a contentId xyz.
Also that means that usually the user table becomes many columns as more tables are added since user is usually in the center of tables. Is this acceptable in nosql relational ?
To get the user given contentId xyz, you run the following query against the Users table:
relatedContentColumnName.objectId = ‘xyz’
where “relatedContentColumnName” is the name of the relation column declared in the Users table.
In the Users table you would have only one column which represents the one-to-many relationship.
Hmm thank you . I will try that.
My only concern is that if I decide to expand the application and now add more related tables for the user. For example address table , purchase table , phones table. As a result, users table will end up having many 1:N columns with one for each table.
In SQL you would add the user column to each one of the tables so it is 1:1. But you are saying that if I do it that way then the above 2 requirement can’t be met , correct ?
In SQL (and in Backendless) you can do it either way. However, if you have a requirement to load multiple child objects for a given parent, then the one-to-many relationship is more appropriate. And it is absolutely OK to have multiple relation columns in the Users (or any other) table.
The 2nd requirement can be done with the whereClause I showed above.
Perfect. Thank you so much. I will start playing with it and let you know if I have further questions. I appreciate the quick response
I have been checking this thread http://support.backendless.com/t/retrieve-user-data-from-ownerid which is very helpful and made me wonder about my case above.
If I create a content in the Content table then the ownerId column that is created by the system will contain the objectId of the user who posted the content.
In that case, can’t I just get user by taking the value of the ownerId of the Content table, isn’t my understanding correct?
I am trying to understand how the whole thing work that’s why I am asking about different approaches
Yes, you can certainly do that. “ownerId” will contain objectId of the BackendlessUser object which represents the user who originally saved the object.