Am i "over engineering relationships?"

This is a question to see if I am using best practice for establishing relationships between files.
I am a bit nervous about posting this as if I am missing something basic, this post will, I am sure, cause quite a deal of hilarity amongst the more experienced community members - so please be gentle if I have got this completely wrong.
The way I have done things actually works… :grimacing: so that side of it is hopefully a positive indicator.

I am using glide tables
As well as establishing relationships between tables, I am also ensuring that all relationships are based on utilising unique identifiers (the rowid in the glide tables), rather than actual data field values.

I was originally using a text field with an actual data value (eg Sydney or Melbourne) as the “foreign key” but then I realised the folly of basing relationships on data that could change and how that would create unwanted side-effects with relationships.
So I converted the entire app to use the row-id of the reference record and stored that actual row-id in the related/relating record.

So instead of storing the data value “Sydney” in the CITY field in the ORGANISATIONS table( and which is entered as a choice from the screen on create or edit), I stored the value of the rowid of the record that contained the value “Sydney” in the CITY field in the ORGANISATIONS table
I did that by configuring the choice field to the following

This is what the data looks like

And this is what the relationship configuration looks like

There is one more part to this question…
I did this as well but I am thinking it is not necessary.
What I did was I also added a look up field to store the value of the data field that the user would need to see on the screen in order to make their correct choice from the drop down being used- but i think that the choice field configuration functionality takes care of that with the DISPLAY setting in the config panel being set to the data field (LOCATIONS —> CITY), that the rowid points to.
In the data screen shot, you will see a field called (OLD field) OHeadOffice - This is where I had originally stored the data field value to use as the basis of the relationship in the first iteration of the app.
As I needed to go through the entire table and create the (rowid) location foreign key for each row, I set up an additional field RowId_HeadOffice as the field to store that rowid foreign key data. That way as I threaded my way through each row, I could see the old value on the screen as I was editing the record, and then set the new field to the correct value, and storing the rowid as the foreign key into the new field which was set to be the source for the relationship column function.
Comments and suggestions warmly welcomed and greatly appreciated

1 Like

What you have there is perfectly fine, and many would consider it best practice.

2 Likes

Ahhh - Thank you…
It wa actually you that helped me realise @Darren_Murphy that I needed to change everything to use rowids rather than actual data values as foreign keys.
There is aa little bit more that I will put up now as this could be the over engineering part.
I have used anothe field as a lookup and that si the bit that I thnk may be doubling up some of the relationship field fucntionality

1 Like

The only downside to using RowIDs for relations is that it usually means you need extra Lookup columns, which can be a bit of a pain at times.

But in my view the inconvenience of that is more than offset by the advantage of having something more robust and future proof.

3 Likes

Agree completely.
Now is there any protection for the following

  1. Deleting a record from a reference file that has its rowid stored as a foreign key in a related row. (IS that what they mean by referential integrity.

Is this line of thinking correct?
By using rowid, I realised that if a person on an organisation’s database changes their name, (esp if one has had their “old” name used extensively all through the database as a foreign key) - for example if that person was a contact on many legal cases or similar, updating the name would be a major effort. Using rowid as the actual foreign key - you just change the name on the reference row, and whenever the foreign key is referenced going forward, it will bring up the updated name.
Is that correct?

1 Like

Yes, except Glide doesn’t enforce referential integrity in the way that a proper relational database would, so you have to manage that yourself. Assuming that you would want all related records removed whenever a parent record is removed, you can do it with a custom action as follows:

  • First, ensure that you have a multiple relation that matches all the related child records
  • Start your action with a Delete Row step, targeting the multiple relation. This will delete all the related records and will work as long as they are not in a Big Table.
  • Then add a Wait for Condition, waiting until the relation is empty
  • Finally, another Delete Row step, removing the parent record (This item)

The wait for condition step is necessary, to ensure that the parent isn’t deleted before all child records are removed. Because deleting the parent will break the relation, which could result in orphaned child records otherwise.

Yep, absolutely.
It’s a principle of good database design that there should be a single source of truth for all data, and data duplication should always be avoided whereever possible.

You might find the following video interesting. A lot of what it teaches can be applied in Glide.

3 Likes

Thanks so much @Darren_Murphy - I can’t thank you enough as I realise at times, despite thinking that progress is being made, it is so important to be able to check in with a guide/mentor (as we can iin this community) and have such dedicated and highly knowledgeable experts such as yourself willing to review our questions and devote your time to our growth and progression - I really can’t thank you enough Darren.

2 Likes