Table relationships and Object relationships

General
1

Hello - I posted this to the Slack forum but was told that this site is more appropriate.
I’m new to using this type of platform for DB management and trying to work through table relationships. I am used to creating single joins between primary keys and that is it. I have read the documentation and viewed some of Mark’s videos (very helpful).

Is it necessary to relate the objects in each table? I’m assuming that the only way the object data can be passed along in the frontend is if they are referenced in the backend.

When I loaded some data from an existing DB I had to go through and manually establish the links for each record (I have a Customer and Order table). I added a new record to the order table to test things and I expected that it would automatically create the link for the objects. But it did not. Is this something that is only done from the front end when a new record is added. Or have I missed something critical :disappointed:

Thank you
Ray

2

Hi Ray,

Let me address a few things so we can get on the same page.

Is it necessary to relate the objects in each table?

No, using relations is not necessary, but using relations leads to cleaner database design, which in turn leads to simpler data management. Consider the following example:

image
image757×381 35.7 KB

In there we have a one-to-one relation between the Person and the Address table. Every person object may have a related address. Is it necessary to do it that way? No, you could alternatively have the following way where the city name is a part of the Person table:
image
It may be OK with something as simple as this, however, in real applications where you may have a dozen of entities, flattening database structures this way would result in a number of problems such as performance, duplication of data, inability to understand how one entity relates to another.

I’m assuming that the only way the object data can be passed along in the frontend is if they are referenced in the backend.

Data in the frontend needs to come from somewhere. If the data resides in the database, then yes, it needs to come from the backend.

I added a new record to the order table to test things and I expected that it would automatically create the link for the objects

How would it automatically know what customer that order is for? When you save an order, you save just order. The relationship to a customer is done by establishing a relationship between an order that is saved in the database and a customer that is also in there.

I hope this brings some clarity. If not, please ask.

Regards,
Mark

3

Hi Mark - thank you. There are a few moving parts here so I will try to narrow the discussion. I watched your videos on creating relationships between tables and a second one on relating objects.

From the second one using my data here is a bit of the Customers table with the orders column:

image
image843×408 20.9 KB

The missing rows from the orders column are because there aren’t any orders for those customers.

When I add a new order all of the data needed to make the relationship is in the order table. I think this is where I’m struggling - normal relational DB keeps track of the link with 2 foreign key values. So in my mind if I created an order using the Elk Lake Veterinary clinic the new record would have the customerid or objectid to create the relation. But it doesn’t seem to work like that. If I am adding a new row to the order table how do I create the relations link in the orders column of the Customer table? What do I need to insert into the orders column (Customer table)? Looks like it might be the object id from the new order row. This is all part of the front end code correct? Again I’m used to working directly on a database where I could just add a row in one table and it connects through the key.

I may not be explaining this clearly enough… once I get this in my head I should be good to go. Thanks for your assistance.

4

This is my table with its relationship. Is the connection between tables made using the objectid of each record? Is the objectid acting like a Foreign Key?

image

5

One more bit of info while I have this in my head:

image

On the video that explains setting up this relation there is a small checkbox with a label of autoload in the area where is says “RELATION TO Orders”. That box doesn’t show up for me.

Cheers

6

Hi @Ray_Rogers !

If I am adding a new row to the order table how do I create the relations link in the orders column of the Customer table? What do I need to insert into the orders column (Customer table)?

At the current moment you need to set relations explicitly via separate API call. Here is the doc for the REST API - Set/Add Relation with objects - Backendless REST API Documentation . In documentation you can find also descriptions for other our SDKs

This is my table with its relationship. Is the connection between tables made using the objectid of each record?

Yes, you are correct. For relation used objectIds of related records.

Is the objectid acting like a Foreign Key?

Not exactly. Backendless uses a litle bit more complicated mechanic under the hood. For each relation Backendless creates internal table which holds info about relations between objects. This internal table has foreign keys for each of the tables (in your case such internal table would be created with foreign keys for Customers and Orders tables).

On the video that explains setting up this relation there is a small checkbox with a label of autoload in the area where is says “RELATION TO Orders”. That box doesn’t show up for me.

Autoload feature was deprecated some time ago. It is not available for new apps. To load relations you should now explicitly specify relation name in the query.

Regards, Andriy

7

Hello Andriy - thank you for the information. You have filled in some gaps. I’m an old dog trying to learn new tricks :slight_smile:

Regarding the column that references both tables (in my case the orders column in the Orders table - see the image below:
image

If I mouse over the Relations link there is information about the number of objects with instruction to click on the link to see the records. I believe this table is the internal table you referenced previously. I looked at it and found it to contain what you described.

My question is about adding new references from the frontend. If I add a new order into the Orders table I create a new object with a unique objectid. This objectid from the new record needs to be added to the Customer table orders column.

Workflow: add record to orders table, capture the new objectid, capture the customer objectid, find the record for the customer, add the new objectid to the orders column

Does this seem correct to you? Or is there more that I need to make sure the relationship is established?

Thanks for your patience - I need to have this securely in my head before I add an order details reference and make it more complicated.

Be safe as always
Ray

Have I got the workflow correct?

8

It is almost correct except that you don’t need to add new order ID to the orders column of customer directly. After order was created and you obtained its ID, you should set relations via separate API call (Set/Add Relation with objects - Backendless REST API Documentation or corresponding SDK method). When "set relations " API Call was executed without errors you can be sure that relation between records were established.

Regards, Andriy

9

Awesome - thank you. I will review that documentation and start working on some routines.

Wonderful support, much appreciated.

Ray