Forcing one-to-one database relationships

Hi There,

Does Appsheet have an in-built elegant way to enforce a one-to-one data relationship constraint (to follow the database design)?

I have searched long and hard and it seems people are hacking a solution by setting the Foreign Key field in the child table (Appsheet settings) as the Primary Key for that table.

Works, but seems inelegant with possible confusion re table design down the line.
Is that the only way?

We also tried data validations -
This one worked for creation of new records:
NOT(IN([_THIS],dbo.tablename[tablename_id]))
but unfortunately it also prevents you from editing an existing record of courseโ€ฆ

Perhaps someone else has a cracked a better data validation formula that can also work on editing a record?

Or Iโ€™ve missed the right way to enforce database table relationships in Appsheet?

Many Thanks!

Solved Solved
0 30 4,112
1 ACCEPTED SOLUTION

Hi @candicepelser,

As per my understanding, your valid_if approach is correct and just that you are facing a problem that you cannot edit the existing child record due to valid_if expression you are using.

The expression suggested in the article below removes the existing record from the valid_if constraint and thus it allows the existing record to be edited for other values except adding another child record with the same parent key.

Please use the expression suggested in the article below under section " Preventing Duplicate Field Values" and use the exression suggested in the valid_if of the reference column that references parent table.

I tested it on a small test app and it works. Hope its helps.

View solution in original post

30 REPLIES 30

So youโ€™re trying to make it so you can only create 1 subtable entry?

Yes

Iโ€™ve done this for relating Google Form responses to data in another table, where I wanted to ensure a 1 to 1 relationship.

Try using a workflow to add a record to the second table, using the same key as the first table. Donโ€™t allow the users to add records to the second table. Then every new record in table 1 will create a matching record in table 2.

[EDIT] Forgot to mentionโ€“
โ€ฆthen create a virtual column for the Ref in Table 2. The Ref column should be in LIST format, but you only want the one record with a matching key.
LIST([Key]) or {[Key]}

So one way I have done something similar is to have 2 list columns pointing at the subtable

The top one only shows when [Related Tables] is blank. It basically is the default and just points at the entire subtable.

The bottom list column only shows when [Related Tables] is not blank. Rather than pointing at the table, it points to a slice set to allow Updates Only

Just name both list columns the same and it looks good to the user. But doesnโ€™t really tick the elegant option youโ€™re looking for. Hope it helps though

Simon@1minManager.com

Thanks@ 1minManager,
Appreciate the input.
As you mentioned however, it may work but isnโ€™t elegant - which practically means thereโ€™s more complexity down the line.

Hi @candicepelser,

As per my understanding, your valid_if approach is correct and just that you are facing a problem that you cannot edit the existing child record due to valid_if expression you are using.

The expression suggested in the article below removes the existing record from the valid_if constraint and thus it allows the existing record to be edited for other values except adding another child record with the same parent key.

Please use the expression suggested in the article below under section " Preventing Duplicate Field Values" and use the exression suggested in the valid_if of the reference column that references parent table.

I tested it on a small test app and it works. Hope its helps.

So your valid_if expression in the reference column in child table will be something like

NOT(IN([_THIS], SELECT(Child Table [Reference Column], NOT(IN([Key Column of Child Table], LIST([_THISROW].[Key Column of Child Table]))))))

Thanks @Suvrutt_Gurjar , your suggestion is the most elegant & practical. With the added benefit that one can give the user guidance using the error message.

It is surprising that AppSheet - the appbuilder which is designed with the data model in mindโ€ฆ doesnโ€™t have this as an in-built functionality.

Even better if Appsheet could just pick up such constraints from the Database itself - an obvious choice for future functionality to consider.

Very much appreciate your help!

Just a side note in generallyโ€ฆ if two or more users are adding a child record at the same time, it will mess your one-to-one relationship because then you will have two child records for the parent.

Thanks @Aleksi,

Good point. In that caseโ€ฆ What do you suggest instead?

It depends what kind of process you have. Are you adding parent and child records at the same time or are you first adding the parent and after hours or days, you will add the child?

First the parent is added, then the child (could be a few minutes or many days later)

Who can add the child, doesnโ€™t need to be the same user? It can be whoever?

Yes thatโ€™s correct. The real-world situation is this:

A Turtle Nest Monitor on patrol at night, spots a nesting turtle and records details of the Turtle & the Nest she laysโ€ฆ
Nest = Parent Record

Many days later the nest begins to hatch, and whichever Nest Monitor is on duty, will record the details of the Hatching (Child Record)

There can only ever be one Hatching record for a Nest record. And a hatching can only be captured after the Nest has been captured, because it is associated with that Nest.

[I knowโ€ฆ turtle nesting app - what an awesome project to work on! )

I would just set the behaviour expression of the add action to something like SUM([Related Hatchings][IDColumn]) < 1. This would remove the ability to add more child values effectively creating a 1 to 1 relationship. Since it seems like there would just be one nest monitor acting on a single nest site at a time I dont think there would be a conflict of two people adding a child at the same time.

How many columns do you have in each table? If the tables arenโ€™t too large, perhaps you could merge them, putting all data in a single row. Then use Slices to split the data into Nest/Hatchling.

You can also use a Virtual Column Ref to relate the record back to itself, and display the Hatchling slice as if it were a child record using this method:
https://community.appsheet.com/t/detail-views-space-saving-trick-to-display-columns-horizontally/144...

As Heino mentioned, it sounds that itโ€™s probably not the issue. Though as a precaution you could create a scheduled report to send an email if duplicate records are added.

100% for it being in the same table for data capture. You could even run slices to split them out for different formsโ€ฆ and then you could even double back and use the add a row action feature to create the child recordโ€ฆ (All without the user being the wiser.)

@GreenFlux @Grant_Stead

Making a child table part of the parent table - just to suit the app-builder, is a bad idea, ifโ€ฆ a) you expect your data set to grow substantially and performance is going to become more and more important, and b) when youโ€™re not sure the app-builder is going to serve you longer term as you growโ€ฆ

From my 25 years of IT application building experience - breaking good database design principles is a recipe for suffering down the line.

@Aleksi - Appsheet has a great proposition in the market of app-builders, it seems to be unique in following a sort of โ€˜data-model-upโ€™ design. Which saves a huge amount of time on app development!
Iโ€™m not sure what your long term strategy is over there at Appsheet, but this one seems like a no-brainer given AppSheetโ€™s current design. Have the ability to set relationship constraints between tables in Appsheet - in the same way that one does in a DB.

Thanks again everyone for your time and support on this!

agree.
The feature request has been requested many times. Even I have made the formal request beforeโ€ฆ

Mark_Krug
Participant I

Relationship constraintsโ€ฆsupported.

Hello everyone! Iโ€™m not sure who will get notified of my reply since itโ€™s been nearly two years since the last comment, but I am interested to know if this feature is now offered in AppSheet. I have looked throughout the community but this is the best comment section regarding the one-to-one relationship. So far, the best option I found is using slices. Is that still the case? Or is there a better way?

I am new to this community, joined yesterday actually , so bear with me if this feature is now offered in AppSheet and my question seems redundant.

Thank you! Hope all are well and safe!

There have been no better solution yet than Slices.
That comes with itโ€™s own problems.
But, anyway, there are very skilled people here that could share their experiences about it.

@Marc_Dillon @MultiTech_Visions @Steve @1minManager @GreenFlux @Suvrutt_Gurjar @WillowMobileSystems
Just to tag a few.

PS: Sorry guys, you can ignore if you want

I have come across this thread multiple times and have been reluctant to respond. Now, I would like to add my two cents worth.

In a database there are good reasons to have a one-to-one relationship. But what are they? In my experience, it boils down to the data being segregated for use in OTHER relationships within the database - many-to-one or many-to-many. It allows for reduction of the data footprint when re-used in other schemas - not the one-to-one but the others. The smaller data footprint helps with performance.

But AppSheet doesnโ€™t allow multiple parents - yet. So, IMHO, forcing a one-to-one relationship is a waste of performance resources. I.e. the cost to maintain the relationship between Parent and Child (especially with synchronizing data/calculations) is simply not worth it.

So, generally speaking, the answer is to simply include the Parent and Child data together in a single row.

Slices are akin to Views in databases. They are extremely helpful in reducing the visualized data but nowadays we can control that from the views themselves. They can also be helpful, on the device side, by:

  1. removing columns to prevent App Formulas from being fired.
  2. allowing multiple different views on the same data rather then using the AppSheet defaults.

I am not sure where Slices create problems. Typically they would be used to resolve a problem.

Now, I said โ€œgenerally speakingโ€โ€ฆI am sure there are use cases where a one-to-one relationship is the way to go. I just canโ€™t think of any.

Hello!
I do appreciate everyone taking some time of their day to pitch in on this topic.

John, Iโ€™m not savvy in technical terms for AppSheet yet - I hope I can explain my personal situation well.
To give an example that is easy to understand, iโ€™d like to compare my data to inspection for a construction job.
The Parent data would include the property address along with the customer information, date of insp., type of insp., etc. all the info before the inspection.
The Child data would be everything after the inspection which I would like to break apart (one to one relationship). For example, the Report would be a Child Data (who did the inspection, their findings, cost of work, the report itself, etc.) The Property Description (size of prop., type of roof, Occ. or vacant, etc.) would be another Child Data. If the job is approved, then Work Ordered is another Child Data (what work is approved (all or parts of the work), date of work(s), whoโ€™s doing the work, etc.) Other Child Datas are Job Expenses and Completion of Work. I would also have Photo Gallery Data which would be a one-to-many which is the standard method that works well.

The information for one-to-one Child Data would not have multiple entries (there wouldnโ€™t be multiple Property Descriptions, Reports, Job Expenses, or Completion of Work). If a new report is made by a different inspector but for the same address, then a new key would be given for that report which will have new findings, job expenses, photos, etc)

Having slices for my data would take up a lot of columns for the Parent Data. Also, not every job is approved, so Work Ordered, Job Expenses, Completion Child Datas do not need to be added. Slices are effective, I just wanted to make it neater.

I hope I explained it well.
Let me know if thereโ€™s anything I can expand on.
Thank you for your time!

I think there is a mis-understanding of what a one-to-one relationship truly means versus, as was mentioned before, reference to a single data row. In case you donโ€™t know, One-to-one is term used in data modeling across the industry. Itโ€™s not an AppSheet specific term.

The best example I have seen of a one-to-one is Employee and Company Car with the understanding that the car is not shared. An Employee is assigned only 1 car and each car can only be assigned to a single employee. That car can be re-assigned to another employee later but the enforcement of the one-to-one relationship would REQUIRE that the current assignment of the car be REMOVED first. If you wanted to track the history of who was assigned that car, it would show in a history table dedicated for that.

In your description, the only data I see that might meet the one-to-one relationship criteria is Inspection and Report assuming the report contains details about that one inspection only. I say MIGHT because I can imagine a case where the Inspection data changes causing the report to run again. Instead of forcing a one-to-one relationship here, a better model would be to have only 1 ACTIVE report row. It would be a pain to have to remove and then re-insert the report row which is what a one-to-one would imply.

Property Description - I completely understand that the Inspection can occur for only a single property BUT that property could be the subject of multiple Inspections - not a one-to-one.

For Work Ordered, Job Expenses and Completion of Work, obviously there could be multiples of these resulting from the inspection - again not a one-to-one.


Data modeling is a bit of an art. I can tell that you have given your data schema some thought and have a good high level breakdown of your data structure. you are on the right track

But I donโ€™t think one-to-one relationships are what you want. Instead youโ€™ll want to make use of REF columns. Report would have a REF column to Inspections. Inspections would have a REF column to Property. Work Ordered, Job Expenses and Completion of Work (not really sure what this is) would all be CHILD tables to the Inspection I would think.

Bottom line, use your intuition and use the tools AppSheet has available, with lots of questions along the way and you be just fine. Donโ€™t worry about the technical terms of data modeling, AppSheet abstracts a lot that away so you donโ€™t have to worry about it.

Good luck!

I apologize, Iโ€™ve been looking through many forums and the one-to-one relationship seemed the one that fit the best for what I was trying to find. I just started coding journey this year so I do apologize for the inexperience!
I really appreciate the comparison and your time for explaining.

Iโ€™ll definitely be asking many questions and dive deeper into all the tools Appsheet has to offer.

Thanks again!

No need to apologize. My response was simply to set the record straight, for you and for anyone reading this post in the future. I feel itโ€™s important for us all to have the best understanding. As such, I am not always right or correct and welcome any criticism or corrective knowledge.

Iโ€™d be curious to know exactly what sort of feature/addition do you all think could be added to Appsheet to enforce a one-to-one relationship? @Grant_Stead , care to comment, or is there an existing feature request for this?

In my opinion, we already have the necessary tools to enforce it.

I would also lend my suggestion to combine records into the same Table whenever it makes sense to. Of course, sometimes it does not make sense, in which case you can use the solutions already provided.

Currently, there is a simple REF fieldโ€ฆ This automatically creates the REFROWS rightโ€ฆ
We can also create a ENUM basetype REF, which for all intents and purposes simply tells appsheet to NOT auto create the REFROWS. (But, everything else in AS treats it like itโ€™s a REF)
Seems like we would be better off with options under the REF column typeโ€ฆ
many-to-one (Standard)
one-to-one (Which would basically not allow referenced adds after one is addedโ€ฆ)
Maybe even an option there to โ€œCreate Child REFROWSโ€
โ€œUse REF Labelsโ€
etc.

Top Labels in this Space