Understanding Relations

[Jeroen Jeroen]

I’m confused about how relations between tables work in Tap Forms.

The basic relation in databases I know is the one where an A-record can link to a B-record through a column like A_B_ID. This is what I know to be ‘one-to-many’ (or ‘many-to-one’?)

However, I can’t seem to create this in TF. The closest I’ve come is creating a one-to-many relation in the B-table and showing the reverse column in the A-table. However, this is not the same: I can still create multiple links to the same record in the A-table, they’re simple not shown in the reverse column. Also, I don’t understand why I need to create this relationship from the reverse table.

I feel like I’m missing something. Is this the correct way to create one-to-many relations? If so, why?

[Brendan]

Hello Jeroen,

I always create my one-to-many relationships from the parent form, not from the child form. You can enable the Show Inverse Relationship after and Tap Forms will add a field to the child form to show the inverse relationship. Also, in Tap Forms the only relationship that uses a field to establish the join is the Join Link Type. The others are done via the user selecting which records to link. But with a Join Link Type, it behaves more like a many-to-many.

There’s information about relationships here:

https://www.tapforms.com/help-mac/5.3/en/topic/relationships

Thanks!

Brendan

[Jeroen Jeroen]

Ok so I am doing it right. One-to-many relations should be made from the ‘many’ (parent) rather than from the ‘one’ (child) and then you can select a relation from the ‘reverse’ field in the child. But why? Why not the other way around, like in any other database? It seems unnecessarily complicated. Also like I said, it’s more error-prone, because when you create the link from the parent, you can still select the same child from multiple parents, which makes it not really one-to-many.

[Sam Moffatt]

I just did a quick test where if I tried to link a child record to a new parent record, the old parent record didn’t display it and the child record reported that the parent record was the new parent record. That seems to be functioning as a one to many structure? To be honest being able to search and link child records from the parent form is one of Tap Forms’ more powerful features for me.

Taking a step lower the underlying data model uses distinct join records, keeping in mind that Tap Forms is built on a document store (CouchbaseLite) rather than a traditional database. One of the advantages of this is that it avoids a rewrite of the child record when links are manipulated which makes sync more efficient and avoids unnecessary write conflicts on link changes. This isn’t too dissimilar to an approach you’d use to make a many to many JOIN work in a more traditional database too where you’d have unique ID’s of both tables combined into a third table. Tap Forms also supports a many to many relationship (easy to implement since it’s the same structure without a uniqueness constraint) as well as a JOIN mechanism for joining on field values in forms directly (useful when importing data that already has keys).

You’re right in a traditional database that you could model a one to many relationship in such a way that the parent is unaware of the child, though as noted if you did a many to many you’d end up with a third table to maintain that relationship not dissimilar to Tap Forms’ approach. However for someone not with a background in database systems, I actually think the way Brendan’s structured it makes more sense because it’s emphasis includes where this is displayed in the user interface. A traditional relational database isn’t concerned with presentation, it doesn’t need to know how to tell you the child records of a parent table. As you point out, there isn’t inherently a way of actually doing that. One can infer that meaning from the schema to an extent by examining the structure of all of the tables but generally there is an application on top concerned with the presentation of that data. In a sense Tap Forms blurs the lines because Tap Forms is concerned with presentation as well as obscuring to an extent the underlying data model.

I’d argue that’s it’s bigger selling point: a friendly user interface to store structured data that is relatively easy to use. For me I felt it made sense to create it that way because I’m generally more interested in having multiple child records show up in my parent. That’s predominantly how I navigate and I suspect many modelling use cases are actually the same. For the 1:M relationship that’s an inversion on modelling it with the parent record ID stored as a field in the child record but when you add in M:M records it’s actually a not uncommon implementation approach, especially in a NoSQL document store world where it is up to the application to enforce referential integrity on it’s own data (not to mention in a world where you aren’t assured that distinct records will be sync’d linearly either).

[Brendan]

It just made more sense to me to use a top-down approach. A parent can have many children, but in a one-to-many, a child can have only one parent. So display the parent record and see all of its children. Display a child record and see it’s only parent. That doesn’t seem complicated to me at all.

When you say “from the (many) parent”, that’s not how I see the parent. The parent is the “one”.

Like in an Order –> Order Items relationship. There is one Order record, which has many Order Item child records.

[Jeroen Jeroen]

Thanks for the extended explanation Sam and Brendan. It makes more sense now. Indeed, in a ‘traditional’ database, many-to-many would need an additional table and I see how that is not user friendly.

I guess one of the main reasons I wanted it to work the other way around, is because now there is no easy way to make the 1:M-relation from the child mandatory or check if it’s empty. How would you go about this in Tap Forms?

To come back to the problem of multiple linked records: when I change an 1:M relation to another existing parent, the relation with the old parent indeed disappears. However, when I create a new parent from the child (using the +> button), the old parent still lists the child, even though the child only shows the link to the newly created parent. Can you reproduce that?

[Sam Moffatt]

I don’t think you can make the field mandatory (though Brendan will likely correct if I’m wrong there) however I’d use a script field to check to see if the field is empty. Create a new script field in your child form, jump into the script editor, keep the function template but wipe out the contents of the method, double click on the section heading matching the name of the field and it’ll put in a record.getFieldValue line then you can check if it’s undefined or not. Something like this worked for me with a test form I was using:

function Missing_Parent() {
	var days = record.getFieldValue('fld-429746c8ec6d433d9bb1247a5ddf31bf');
	if (days === undefined) {
		return 1;
	} else {
		return 0;
	}
}

Missing_Parent();

I set the field return on the left of the script editor to be the number type and then you can do a saved search on that field to look for one or zero. You can hide the script field once this is all working to clean up your form display.

I can reproduce the bug with creating a new parent from the child doesn’t unlink the old parent from the child.

[Brendan]

Oops… Guess that’s a bug. Internally a 1:M and M:M are modelled identically in Tap Forms with a separate entity called TFLinkRecord to store the connections between the form, field, and records. It’s application logic that enforces the constraints and I guess I missed one :) Thanks for letting me know.

[Jeroen Jeroen]

Awesome! Thanks again to you both!

[Author]

Posts