Clarification of Relations

[jt]

I have problems wrapping my head around the meaning of the checkboxes on relations. The three checkboxes make me very nervous, and confused.

Allow creation of related records
Allow parent delete when having related records
Delete related records

Of these, the first one is not that difficult to understand. The other two, however, are a different story.

I have a relation, f_equipment_to_f_areas. The SOURCE is f_equipment and the DESTINATION is f_areas. In my ERD, f_areas is the parent. This is where the problems come in. I want to delete a row from f_equipment. This row is connected by a forign key to its ERD parent (f_areas), and the delete should be allowed.

I would like to get this understanding once and for all. And, I would like for Servoy to add/change the nomenclature on the design form for relations to make this more clear. Which table is the PARENT? The SOURCE or the DESTINATION? How should the checkboxes be set to allow this and not delete a row from the ERD parent?

TIA!

[pbakker]

Hi JT,

I’ve updated the documentation of Relations a bit, see http://wiki.servoy.com/display/DOCS/Relation

I hope this gives you a better understanding of how relations work.

I think that your question on which table (datasource) is parent should be clear after reading the docs. If not, please let us know.

Paul

[david]

Errors in the documentation: http://wiki.servoy.com/display/DOCS/Relation

Allow parent delete when having related records: This property indicates if a record can be deleted if it has child records

true: when there are related order records, Servoy will block the delete of the customer record and raise a ServoyException.NO_PARENT_DELETE_WITH_RELATED_RECORDS exception.
false: related records will not block the delete of the parent record

1. You have the true/false flag the wrong way. Probably because the wording is so confusing The examples are wrong as a result as well.

Allow parent delete when having related records overrules Delete related records
The option Allow parent delete when having related records overrules the option Delete related records. This means that if the first option is set to false, the latter option is ignored. Setting the first option to false and the latter option to true will still block the delete.

2. This needs clarification. “Allow parent delete when having related records” does not override “Delete related records” in the current relation. It overrides the “Delete related records” flag (if set to true) of a relation up the chain from the current relation.

3. You should add an example of “circular” relations and how they can be so confusing when trying to delete a record. In the following case:

• customers_to_orders with “Delete related records” = true
• orders_to_customers with “Allow parent delete when having related records” = false

when you try to delete a customer record the second relation will block the delete

jt:
I would like to get this understanding once and for all. And, I would like for Servoy to add/change the nomenclature on the design form for relations to make this more clear. Which table is the PARENT? The SOURCE or the DESTINATION? How should the checkboxes be set to allow this and not delete a row from the ERD parent?

I think the word “parent” in “Allow parent delete when having related records” is what is confusing. The flag is specific to the left-hand side of the relation, but it is easy to think that it applies to the parent of the left-hand side of the relation.

[Robert_Huber]

Hi

As I mentioned before, I think the Human Interface for and the implementation of relation(ship) handling is very weird. Even if you consider only the 2 chechboxes: Allow parent delete when having related records and Delete related records, you get 16 different possible combinations! As a relationship is bidirectional, you have:

Looking from the 1-side of the relationsship:
1:m → Allow parent delete
1:m → Delete related records (which is cascading delete)

But of course, you also have to look from the m-side of the relationship, so you have as well:

m:1 → Allow parent delete
m:1 → Delete related records

So you can set 16 different possibilites with the 2 checkboxes (actually 2 on each side of the relationship). Many of them are completely useless, but nevertheless you can set it. A reasonably good HI should not allow such thing (my opinion only).

And, I have to add, with all the above described possibilities we have NOT the ability to set NULLIFY for a relation, which is a common situation in data modelling theory and of course very often to be implemented in reality.

Needless to say I would like to see a reworked relationship handling in Servoy including NULLIFYing a relationship.

Regards, Robert

[Robert_Huber]

Hi David

david:
3) You should add an example of “circular” relations and how they can be so confusing when trying to delete a record. In the following case:

• customers_to_orders with “Delete related records” = true
• orders_to_customers with “Allow parent delete when having related records” = false

when you try to delete a customer record the second relation will block the delete

I assume the term circular means a bidirectional relationship? In the data modelling stage, a relationship is per definition bidirectional. It’s an implementation form of handling relationship as it’s done in Servoy, allowing to implement only one way of the relation. But it has consequences to do that, and may be that’s what many people are not aware of. And, as your samples imply, consider implementing only “half” the relation, i. e. one way, and at a later version of your software, add the second half of the relation, you may change your whole behaviour! So one actually should always implement a bidirectional relation, and define the desired behaviour with the checkboxes, which in itself is not very easy (as described in my other reply to this thread.
By the way, in this case, good documentation does not make a not so good implementation more useful, but of course may help to overcome it and hopefully lead to a better implementation

Regards, Robert

[pbakker]

@David: 1:sorted, 2: don’t get your point, 3: Will look into it (although I think the second scenario already covers the point)

@Robert: Relations in Servoy are NOT bi-directional. As for the nullify option: did you register the feature request for it?

Paul

[Robert_Huber]

Hi Paul

pbakker:
@Robert: Relations in Servoy are NOT bi-directional. As for the nullify option: did you register the feature request for it?

Paul

Yes, I know they are not bidirectional in Servoy. In Servoy, one can implement it in one direction, the other direction, or create 2 relations to cover the standard case for relationships. But let’s say one implements the standard (in the database design stage modelled bidirectional relationship), you can set with the current Servoy Human Interface, i. e. the checkboxes, 16 different cases (in the above reply described). We checked them all and what happens in each case, i. e. their corresponding behaviour! In my opinion not very easy to get, and you have to do that to fully understand what’s going on in each case. And definitely not the best HI design to accomplish the “relation task”, but as I said, it’s my opinion only, others may see it differently.

Concerning the nullify option, I did make a feature request in the support system, case # is 269152.

Best regards, Robert

[pbakker]

In Servoy, one can implement it in one direction, the other direction, or create 2 relations to cover the standard case for relationships

I think that once you start looking at Relations in Servoy as one directional, going from “One Record” (parent/source) to “Zero or More Related Records” (child/destination), regardless of what it represents underneat (PK_to_FK or vise versa or something completely different), it’s pretty obvious and the options are clear as well.

But, as always we welcome suggestions. If you have idea’s on how to improve relations setup that fit within and extend on the current options, we are interested.

Paul

[david]

pbakker:
@David: 1:sorted, 2: don’t get your point, 3: Will look into it (although I think the second scenario already covers the point)

1- Example 2 is still wrong.

2- I’m saying this statement is not correct by implication:

Allow parent delete when having related records overrules Delete related records
The option Allow parent delete when having related records overrules the option Delete related records. This means that if the first option is set to false, the latter option is ignored. Setting the first option to false and the latter option to true will still block the delete.

The way it is worded makes it sound like “Allow parent delete when having related records” overrules “Delete related records” for that relation. The fact is, the two have nothing to do with each other in the current relation. “Allow parent delete when having related records” only overrules “Delete related records” for relations “up” the relation chain from the current one. This is an important distinction.

[david]

Robert Huber:
I assume the term circular means a bidirectional relationship?

Yes, and you’re saying it much better than me. I definitely can see how implementing bidirectional relationships would simplify things a lot. Gets my vote. Oh sorry, I can’t vote.

Robert Huber:
By the way, in this case, good documentation does not make a not so good implementation more useful, but of course may help to overcome it and hopefully lead to a better implementation

@Paul: I think this whole article can use a lot more “best practices” pointers to clarify things. Like you have with:

Use Left outer join
In most scenario’s the “Left outer join” type is the join type to use for relations

I think the main thing to point out is that unless you’re implementing a business rule, you should have ALL the checkboxes checked when creating a relationship. This will not mess up any current business rules implemented in other relations. If any of the checkboxes are left unchecked, you are implementing a business rule – with potential consequences to many other relations – and you should know what you’re doing at this point.

Lastly, the recommended defaults should be what is selected when a new relationship is created. Left outer join and all the checkboxes should be selected by default. The current defaults are messing developers up right and left.

[Robert_Huber]

Hi Paul

pbakker:

In Servoy, one can implement it in one direction, the other direction, or create 2 relations to cover the standard case for relationships

I think that once you start looking at Relations in Servoy as one directional, going from “One Record” (parent/source) to “Zero or More Related Records” (child/destination), regardless of what it represents underneat (PK_to_FK or vise versa or something completely different), it’s pretty obvious and the options are clear as well.

Paul

Sorry but I don’t agree about “obvious” and “clear”.

Let me try to explain.

We have a relation 1:m. In database modelling theory often called a master - detail or parent - child situation.

Now you implement the second relation from the m to the 1 side - setting the checkbox Allow parent delete. But the parent is actually the child record! If you discuss such a situation with another developer, you have to take very very much care what you are talking about, otherwise it’s a mess.

Just my experience, regards, Robert

[david]

Robert Huber:
Hi Paul

pbakker:

In Servoy, one can implement it in one direction, the other direction, or create 2 relations to cover the standard case for relationships

I think that once you start looking at Relations in Servoy as one directional, going from “One Record” (parent/source) to “Zero or More Related Records” (child/destination), regardless of what it represents underneat (PK_to_FK or vise versa or something completely different), it’s pretty obvious and the options are clear as well.

Paul

Sorry but I don’t agree about “obvious” and “clear”.

Let me try to explain.

We have a relation 1:m. In database modelling theory often called a master - detail or parent - child situation.

Now you implement the second relation from the m to the 1 side - setting the checkbox Allow parent delete. But the parent is actually the child record! If you discuss such a situation with another developer, you have to take very very much care what you are talking about, otherwise it’s a mess.

Just my experience, regards, Robert

Bingo. For me it takes the form of an IM going something like, “Help! I know this is a stupid question but I can’t figure out why a record is not deleting. The code looks like all my other code…”. I’ve answered this question many times.

[pbakker]

The way it is worded makes it sound like “Allow parent delete when having related records” overrules “Delete related records” for that relation. The fact is, the two have nothing to do with each other in the current relation. “Allow parent delete when having related records” only overrules “Delete related records” for relations “up” the relation chain from the current one. This is an important distinction.

Maybe we’re hitting a language barrier here, because as far as I can see what I wrote is 100% correct: If you set “Allow parent delete when having related records” to false, the “Delete related records” setting is ignored.

For example:

• relation customer_to_orders, allowParentDeleteWhenHavingRelatedRecords=false, deleteRelatedRecords=true
  When you your try to delete a customer record which has related orders, the delete will fail, because allowParentDeleteWhenHavingRelatedRecords is set to false.

It DOESN’T work like: ok, allowParentDeleteWhenHavingRelatedRecords is false, but deleteRelatedRecords is set to true, so lets delete all related records first and then when there are no related records anymore the allowParentDeleteWhenHavingRelatedRecords which is set to false won’t block the delete anymore…

I think the main thing to point out is that unless you’re implementing a business rule, you should have ALL the checkboxes checked when creating a relationship.

Can’t disagree with you more: you should use your brain when creating a relation and the settings should reflect your datamodel If you have a relation “order_to_customer” that return the customer that the order belongs to, it would be plain wrong to set deleteRelatedRecords to true: If I delete an order, I do not want the customer to be deleted! And I do not want the relation to block the delete of my order record either, so allowParentDeleteWhenHavingRelatedRecords should be true as well.

As said before; if you have suggestions how to improve things, please let us know by registering a feature request.

[Robert_Huber]

Hi David

Isn’t the term business rule somewhat vague together with database design. And if you do, I would consider cascading delete, i. e. delete related records (in Servoy term) as a business rule. What would you call it?
So to me this is in contradictionary to what you say with checking ALL relation checkboxes to have no business rules.

Regards, Robert

david:
I think the main thing to point out is that unless you’re implementing a business rule, you should have ALL the checkboxes checked when creating a relationship. This will not mess up any current business rules implemented in other relations. If any of the checkboxes are left unchecked, you are implementing a business rule – with potential consequences to many other relations – and you should know what you’re doing at this point.

Lastly, the recommended defaults should be what is selected when a new relationship is created. Left outer join and all the checkboxes should be selected by default. The current defaults are messing developers up right and left.

[david]

Robert Huber:
Hi David

Isn’t the term business rule somewhat vague together with database design. And if you do, I would consider cascading delete, i. e. delete related records (in Servoy term) as a business rule. What would you call it?
So to me this is in contradictionary to what you say with checking ALL relation checkboxes to have no business rules.

Regards, Robert

david:
I think the main thing to point out is that unless you’re implementing a business rule, you should have ALL the checkboxes checked when creating a relationship. This will not mess up any current business rules implemented in other relations. If any of the checkboxes are left unchecked, you are implementing a business rule – with potential consequences to many other relations – and you should know what you’re doing at this point.

Lastly, the recommended defaults should be what is selected when a new relationship is created. Left outer join and all the checkboxes should be selected by default. The current defaults are messing developers up right and left.

True, cascading delete can be considered a business rule. In that the mere presence of a relation is a business rule.

My point is that from a best practices standpoint, it’s easiest to implement further restrictive business rules starting from cascading delete as your baseline. At least with the way Servoy has the relations setup currently.

[jcompagner]

david:
Yes, and you’re saying it much better than me. I definitely can see how implementing bidirectional relationships would simplify things a lot. Gets my vote. Oh sorry, I can’t vote.

i am curious, how would you see this in scripting…
i define “orders_to_orderlines” and i say i want that bidirectional.

now i am in script are you saying that then

orderrecord.orders_to_orderlines will give you a foundset of the orderlines of that record
and
orderlinesrecord.orders_to_orderlines will give you a foundset with the order record?

Ofcourse this is only possible if they are fully database relations (not with globals on the left side)

problem with this is that i can be quite confusing… Now i use the name orders_to_orderlines but how would you name the above so that it is always clear in the code
which direction it is?

My view about those 2 properties: allow parent delete and delete related records.
Those only really make sense in PK->FK (or PK-PK) relations…

in an FK->PK relation i dont see the FK part (orderlines) as a parent of the PK (orders)… so an FK record you can delete just fine even if you have a relation thats called orderlines_to_orders
and the same goes for auto delete the PK record if you delete the FK record, thats not what you want… if i delete a orderlines record i dont want to auto delete a order record…

[david]

Robert Huber:
Sorry but I don’t agree about “obvious” and “clear”.

Let me try to explain.

We have a relation 1:m. In database modelling theory often called a master - detail or parent - child situation.

Now you implement the second relation from the m to the 1 side - setting the checkbox Allow parent delete. But the parent is actually the child record! If you discuss such a situation with another developer, you have to take very very much care what you are talking about, otherwise it’s a mess.

Just my experience, regards, Robert

I should add that I really like this concept of aligning Servoy relations to how data modeling is thought of. Is there any other IDE tool or technology that mirrors 1-to-1? From my experience, the Servoy implementation is pretty standard.

[david]

jcompagner:
i am curious, how would you see this in scripting…

I see your point – a whole bunch of other issues to think of.

Hmm…what about the concept of two types of relations?

1- Core relations

Bi-directional, implement record delete business rules.

2- Non-core relations

One-to-many, used for programming, filtering, retrieving data, etc. These implement no record delete business rules.

[david]

pbakker:

The way it is worded makes it sound like “Allow parent delete when having related records” overrules “Delete related records” for that relation. The fact is, the two have nothing to do with each other in the current relation. “Allow parent delete when having related records” only overrules “Delete related records” for relations “up” the relation chain from the current one. This is an important distinction.

Maybe we’re hitting a language barrier here, because as far as I can see what I wrote is 100% correct: If you set “Allow parent delete when having related records” to false, the “Delete related records” setting is ignored.

I think a language barrier. You use the term “overrules” which is incorrect for the current relation. You are correct that it does “ignore” but that should be fairly obvious that child records won’t be deleted if the parent can’t be deleted if there are child records…

“Overrules” does apply with cascading relations. “Allow parent delete when having related records” set to false will overrule a “Delete related records” setting any relation “up” the cascading relations path.

So “overrule” and “ignore” are two different things and behave quite differently.

pbakker:

I think the main thing to point out is that unless you’re implementing a business rule, you should have ALL the checkboxes checked when creating a relationship.

Can’t disagree with you more: you should use your brain when creating a relation and the settings should reflect your datamodel If you have a relation “order_to_customer” that return the customer that the order belongs to, it would be plain wrong to set deleteRelatedRecords to true: If I delete an order, I do not want the customer to be deleted! And I do not want the relation to block the delete of my order record either, so allowParentDeleteWhenHavingRelatedRecords should be true as well.

In practice, how many relations implement record delete business rules compared to how many that don’t? In my experience, it’s quite a lopsided count in favor of the relations that don’t.

So while I agree that record delete business rules need to be very carefully thought out – once the core relations are in place that implement these rules all other relations that are used for filtering, grabbing data from multiple relations away, scripting, etc – these should be set to not impact the delete business rules set in the core business rules. And this is where developers trip themselves up ALL the time.

pbakker:
As said before; if you have suggestions how to improve things, please let us know by registering a feature request.

And as I’ve said before, a closed bug/feature request system is a waste of my time. If I can’t see, comment on, test, and vote on what everyone else is entering into the system, it does me very little good. We quit using the bug/feature request system years ago because it’s not useful in any way that is obvious to me.

[Harjo]

And as I’ve said before, a closed bug/feature request system is a waste of my time.

David, I think you are over-reacting…
I posted this morning a bug about controller.loadRecords(myPK) in conjunction with tableFilterParam and it was fixed 4 hours later!!
In Servoy 5.1 final, 6 out of 10 of my feature requests where implemented!

It’s true, the support-system can be much approved and Servoy stated somewhere else, that they are looking into it, but stating that it is a waste of time, is not right, IMHO.