Placing Referential Integrity Rules

[T-Square]

Recently, it came to my attention that one of my solutions had some errors in the referential integrity rules for the data. Specifically, I'd forgotten to ensure that the Delete records option was checked for several of the relationships in my app.

This got me to thinking about the Relationship graph, its difference from an Entity-Relationship graph, and how that might play out with regard to cascading deletes.

The Relationship graph is something of a hybrid, combining an ER diagram and query diagrams. As a consequence, a pair of related tables can have multiple table occurrences with multiple relationships on the graph.

So my question is this: does anyone have practical suggestions on how to select the relationship that gets the referential rule, and then how to indicate in the graph which relationship has the rule applied to it?

Cheers,

David

[Ender]

I would only put the delete option on the primary relationships (those between the primary key in the parent table and the foreign key in the child table.) In my solution, I can tell which TOs are the primaries by the way I name them.

[Søren Dyhr]

As a consequence, a pair of related tables can have multiple table occurrences with multiple relationships on the graph.

Oh dear what a blunder I might be wrong here?? but you might put too much efford into making the relations graph behaving ER'ish ...because it seems like you contradict rule 2 and 3 in this video:

http://previews.filemakermagazine.com/videos/541/GraphRules_full.mov

It does as well seem like you're struggling with one of the downsides with the spider/octopussy graph:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=608&mode=thread&order=0&thold=0

Hopefully do you have full access to this movie?

But Ender is right, one solution could be a stringent naming system, but the differen schools mentioned in this download should be considered:

http://www.filemaker.com/downloads/pdf/FMDev_ConvNov05.pdf

--sd

[T-Square]

Søren--

No blunders; I'm trying to implement part of the function of the RG (to enforce referential integrity) in a principled manner.

What I'm doing in this thread is seeing whether others have methods for managing and tracking this.

Given the nature of the RG, Ender's comment about "primary relationships" is a little intriguing--with multiple TOs for a given table, how do you decide which is "primary"? It seems an arbitrary designation in most cases, since nearly all my relationships are pk/fk.

This discussion takes on more depth when you consider a Separation Model application--do you put the cascading deletes in the data file or the UI?

Cheers,

David

[Ender]

...since nearly all my relationships are pk/fk.

Unless you're using your terms a little loosely, I don't see how this is possible. Perhaps an example TOG would help us.

[Søren Dyhr]

how do you decide which is "primary"?

If using Anchor Bouy, is it the leftmost in each TOG... and the double directonal feature is deliberately ignored. If the other direction needs attention a new TOG have to find it's way into the graph.

--sd

[T-Square]

Ender--

I probably AM misunderstanding the use of the terms. And I exaggerate, too.

Søren--

Left-most. Got it. Now, all I've got to do is untangle the TOG I have, which is all glued together to a central Globals table--a hangover from the app's days in FM6.

Oh yeah, and remember which side is my left!

David

[Ender]

If using Anchor Bouy, is it the leftmost in each TOG...

Huh? Left-Right doesn't have much meaning with the TOG, since TOs could be flipped around.

[Ender]

Hey David,

I'll try to get a little more in-depth here. For me it's pretty obvious what the primary relationships are, but I realized that part of what makes it obvious is the way I name the TOs, where I place the TOs, and how I name the keys.

Take a look at this example Contact-Event structure to see what I'm talking about. These are the conventions I use, and how it helps identify the primary relationships:

1. TOs for the same table are colored the same, but only the primary TO is expanded to show all or many of the fields. The secondary TOs are collapsed.

2. The primary TO is named with the source table's name. The secondary TOs are named "

3. If possible, the primary TOs are kept on the same relative horizontal plane (or planes).

4. The keys follow Core Solutions' convention, where a primary key is indicated by the 'p' in the suffix, and a foreign key is indicated by an 'f' in the suffix. An alternate parent key is indicated by an 'a' in the suffix.

5. The primary relationships are almost always the ones with a simple "=" relationship, with one field on each side between the primary key and the foreign key. Relationships to secondary TOs usually use an alternate key on one side, a global filter on one side, or are multiple criteria relationships.

If your current TOG is a tangle, it may be time to organize it. :

Edited May 18, 2006 by Guest

[Ender]

Here's the original file.

ContactManager.fp7.zip

[Søren Dyhr]

Left-Right doesn't have much meaning with the TOG, since TOs could be flipped around.

In my humble opinion do I dissagre! Well chances are that I may misread this:

“The anchor TO is always the farthest to the left and the supporting buoy TOs cascade off to the right. You can add TOs as you need them, providing access to related data for the anchor TO. In practice, threads rarely get longer than four of five levels deep.”

...since it isn't in my native tounge! The next thing I might be in dire need for a translation, is this:

Does not support bi-directional relation model inherently. The restriction of limiting layouts to only the anchor requires you to create another Anchor Buoy/TOG to express right-to-left data to the layout.

--sd

[comment]

I am afraid you are making a circular argument here:

---

Q: how do you decide which is "primary"?

A: If using Anchor Bouy, is it the leftmost in each TOG.

---

Q: If using Anchor-Buoy, how do you decide which one should be the leftmost in each TOG?

A: (presumably) Always put the primary on the left.

I agree with Ender's definition (primary - foreign), but perhaps it should be pointed out that this is merely a rule-of-thumb. The real test, I think, is that a relationship is "primary" if its removal would result in loss of information.

[Søren Dyhr]

Argument ...isn't that stressing it a bit? I'm airing my rather arbitrary plucking in the whitepapers issued, in search for an answer to my own uncertaincy.

I've now taken a look at the template behind the graph, and need an explanation to following ...a contact has only one job, and performs it from only one location??

--sd

[Ender]

I haven't read your DevCon notes, nor do I use the "Anchor-Bouy" model, but I'm just thinking of a typical many-to-many join. One of the relationships to the join table is a one-to-many and the the other is a many-to-one. It doesn't really matter what the relative order of the TOs is. The relationships involved are either the 'primaries' or 'secondaries', depending not on their left-right order, but on their keys.

Besides, if the developer hasn't been to that DevCon session, they probably wouldn't know about that recommended practice (if that's what it is). They might have put their anchors on the bottom and bouys on top to fit in to the nautical theme a little better. :

I don't think the specific function of my example is too important for this discussion, but the idea is that a Company might have many Locations, and each Contact at that Company would be assigned to one of those Locations.

[T-Square]

Ender--

Thanks for the picture and the suggestions. My TOG IS a mess. I could give you a screen shot, but a) you wouldn't get much out of it, and : it's an embarrassment.

It's a migration from FM6. It's been through a few iterations already, but could use more. Currently, there are 90 relationships, 133 layouts, and 139 scripts, so rooting out which TOs are used where, and how they might be handled more efficiently is something of a bear. Add to that the fact that everything in the graph goes through a Globals table, and it becomes really cranky (and so do I).

(BTW, I continued this structure because whenever I have tried to use GTRR commands in the interface, it seemed to slower than a scripted GoToLayout/FindRecords combo).

David