AppSheet and Relational Databases... Hi, I w...

[ControlProcess_]

AppSheet and Relational Databases…

Hi, I was searching AppSheet documentation to try to understand how exactly AppSheet translates some terms from relational databases…

I mean… AppSheet documentation is about references, parent and child tables…

While in Relational Databases lingo usually we talk about Keys and Foreign Keys, and One-to-One, One-to-Many, Many-to-Many relationships between tables.

I gather for example that the “Ref - Is Part of” creates a One-To-Many relationship?

And the “Ref” without being “A Part of” creates a One-to-One relationship?

[RezaRaoofi]

Ref column is equivalent to “Foreign Key”, regardless of the type of relationship being one-to-one or one-to-many, and “As part of” adds ability to add rows in “many” table in the same form that you are adding row to “one” table, so you can save them at once; otherwise without that option, you need to first add a record on the “one” side and save, prior to adding rows to “many” side. In addition, “As part of” will enforce “cascade delete” too.

[ControlProcess_]

@RezaRaoofi thanks. What about MANY-TO-MANY relationships?

[RezaRaoofi]

On a well designed relational database you should not have many-to-many relationship; each many-to-many relationship should be broken into 2 one-to-many relationships; this is totally a design matter and independent from platform whether it is AppSheet, or not.

[ControlProcess_]

Yes, I suppose so. But I thought any many-to-many relationship was always done with a intermediary A-B B-A table.

It would be interesting if you added more info on how to do that (inside appsheet) on your help docs, as well as maybe a App Example with such Many-To-Many relational database, using of course, the intermediary

table.

[RezaRaoofi]

Yes, you are right, that was what I meant by breaking it into 2 one-to-many. Assuming entity A and entity B have many-to-many relationship, you will add a third intermediary table which has one-to-many relationship with each A and B tables, then on the AppSheet side you will add 2 Ref columns in that table, one refers to table A, and one refers to table B.

[RezaRaoofi]

AppSheet has actually provided a pretty decent article about data modeling; check it out: https://help.appsheet.com/data/data-the-essentials

[ControlProcess_]

Thanks, but that leaves some gaps between how Many-to-Many work and AppSheet inner workings.

For example… a junction table has 2 many-to-one relationships.

However, AppSheet only allow ONE Ref-IsAPartOf column per table.

Also, in AppSheet, from where do you set each of the “one-to-many” relationships?

I have a Risks table. Each Risk has a one-to-many relationship to the Analysis table.

Now, I want each analysis to be able to have different Action Plans. Again a one-to-many relationship.

However, there is also the Year Objectives table. This table can have several Action Plans. (One-to-Many).

Some of these Action Plans will also be used by the Analysys table. Some won´t.

Suppose I am at Risk Analysis table, and I create a new Action Plan.

I am taken to the Action Plan form. Where do I link this Action Plan to the Year Objectives table?

[RezaRaoofi]

Well, I do not exactly know all relationships you mentioned above; your last message kind of sounded like you were thinking out loud and each paragraph was a new thought coming to your mind

As I told you before, in the intermediary table you will have Ref columns referring to one side of the relationship, so the table containing the Ref column is the “many” side.

Regarding Is part of you are right, there is a limit, and only one table can have it.

[ControlProcess_]

Yeah, I guess it was somewhat like thinking out loud lol.

Let me try to rephrase.

I create table A, which has a 1-Many relationship with table B, which has a Many-to-1 relationship to C.

From TableA form, I can create a new item at B.

It will open Table B Form view, where the Ref column will be already

“filled” by the link to Table A.

Is that correct?

Ok. But how do I connect table B to table C?

Does it creates a dropdown list at TableB Form, where I can choose between ALL records from Table C?

What if Table C has a thousand records, and worse, some are equal, since Table C itself is a Many-to-One table to Table D?

(The Label can´t be the Key, so you use the Foreign Key as Label. But Foreign Keys repeat in a One-To-Many)

Anyway, seems like ideal situation would be to enter TableA Form, create new item at TableB but leave empty the relation to Table C.

Then at Table C Form, we should somehow choose that same Table B record already linked to Table A, thus linking C to B, which was already connected to A.

But that seems impossible, as from Table C, the inline table to B will look empty and your only option is to create a NEW record at B, linking B to C, but not being able to connect it to A!!!

Errr… I guess this answer was still too long and complicated

[RezaRaoofi]

Just so you know, I am just a community moderator and I don’t work in AppSheet support. I will read your above message, and get back to you later.

Just one thing I missed out in my last answer was that whatever you check as Label in column structure of referrenced table, that column will be displayed in the Ref column’s drop-down list.