1

This is a fundamental novice level question that will not be short. This is specific to Backendless.

I have a number of scenarios I would like to be able to address, as I am working with a small set of tables that are all interrelated in some form and need to be explored from various directions.

A basic example would be something like PersonTable and AddressTable. PersonTable containing a list of people, with their lastName, firstName, etc. AddressTable containing addresses and their various attributes of streetName, houseNumber, etc.

Let's say I want to provide users two distinct views in a main navigation and allow them to drill down further.

View1: You click "People", you get a list of people from the PersonTable. This list appears in a secondary navigation window. Clicking an individual person will provide you the address/addresses associated with that person.

However, I also want to be able to do this in reverse:

View2: You click "Address", you get a list of addresses from the AddressTable. This list appears in a secondary navigation window. Clicking an individual address will provide you with a person/people associated with that address.

So from a uni-directional approach, there would be a relationship from PeopleTable to AddressTable. This is perfectly well and good for View 1. One query will provide the data for the secondary navigation and the results from that query can include the relationship data needed for the drill down.

However, if I wanted to support View 2, I would have to perform two queries given the direction of the relationship and where I am starting.

If you scale this to a larger set of data with more tables and fields, my concern might become more apparent. Because I want to actually provide some data from the parent of the relationship in the initial secondary navigation item creation. So that means an initial query of that table to list the items, and a query for each individual item (to obtain the data I need from it's parent in the relationship) to complete the data shown in the initial list. (Then clicking an item would provide even more detail). Obviously this relationship can be reversed, and I would then be pulling child data and not parent data, but then when I want to come at the data from the other direction (the other View) I am in the same situation again.

TL;DR: I need to be able to traverse tables in pretty much any direction and drill into data while attempting to minimize the number of queries required to do so for any given case. Is this a case where a large number of relationships is warranted?

Getting to the root of the question: My understanding is that, while Backendless does support them, bi-directional relationships are generally frowned upon (at least in the SQL world).

So, really, what is best practice? Is it simply a logical "Create relationships when they help you reduce queries"?

Doughnut Facecream
  • 11
  • 1

2 Answers2

0

Bidirectional is frowned upon here too, though it does work. You may find a few bugs as it isn't used much.

The reason is that it isn't required, you already know you can make a request to get the inverse content.

But, the reason you should not use them is that auto-loading all of that extra data when you might not use it is more costly than making explicit requests when you do...

Also, you can limit your query impact in terms of network traffic by creating a custom service which does all the leg work.

Wain
  • 118,658
  • 15
  • 128
  • 151
  • Auto-Loading wasn't exactly my concern. I'm concerned with relating records across tables. It seems more efficient to have a field in each table of containing associated records in the other tables. Being that I could pull directly from a specific Address record the exact ID's of the related Person records, instead of having to query the entire PersonTable to find all records with a relation to that specific Address record. It seems like a lower order of complexity to maintain pointers to associated records and my understanding is this is what relations in Backendless are for. – Doughnut Facecream Jun 02 '16 at 22:19
  • You only don't need to query the relationship if you auto load it's data – Wain Jun 02 '16 at 22:49
0

However, if I wanted to support View 2, I would have to perform two queries given the direction of the relationship and where I am starting.

Performing two queries is not necessarily in Backendless, as the query syntax supports "backward lookup". It means knowing a "child" object, you can lookup its parent using the following syntax of the "whereClause":

childRelation.objectId = 'childObjectId'

For example for your Person and Address tables, suppose the relation column in the Parent table is called "addresses" and it is a one-to-many relation. Then the query sent to the Person table is:

addresses.objectId = 'specific-objectId-value-from-Address'

Keep in mind that you can test your whereClause queries using Backendless console. Here's an article about that feature: https://backendless.com/feature-14-sql-based-search-for-data-objects-using-console/

Hope this helps.

Mark Piller
  • 1,046
  • 1
  • 7
  • 6