Referential integrity in 1:1 set.

[CALocklin]

I have a rather unique situation that requires referential integrity on a 1:1 set.

I know there have been issues with referential integrity but I'm wondering if anyone has used it with 1:1 sets and knows whether or not I need to be concerned with those issues in this case?

FWIW: It's actually a "M:1" set where one child record can belong to many parent records but obviously we can't show many parent records at one time so the set actually ends up as 1:1. I know - it's weird but it's necessary in this case.

[Ted Giles]

Re: Referential integrity in 1:1 set.

I'm baffled Cal.
Can you let us know a bit more about the app?

Off the top of my head it looks like you will need a transaction table in the middle, bit like a Library Book app.

Many People
Many Book Titles
One Book Title per Person

[Tom Cone Jr]

Re: Referential integrity in 1:1 set.

I'm a bit puzzled, too.

If RI is enabled and you change the link field value in parent record, the corresponding field value in the linked child will change, too, right? Won't that break the link for some of the "other" parents?

-- tom

[CALocklin]

Re: Referential integrity in 1:1 set.

I'm a bit puzzled, too.

If RI is enabled and you change the link field value in parent record, the corresponding field value in the linked child will change, too, right? Won't that break the link for some of the "other" parents?

-- tom

It would except that I already have a routine to handle that.

This is a surveying application. Each section corner can match up to either 0, 1, or 3 other corners and I'm linking the Notes for "one corner" to all other matching corners because the notes should be the same since it's really one physical corner with multiple 'names'. I already have the [very complex] routine done that updates the other corners if someone (a) creates a new entry for a corner that didn't exist before, (b) deletes a corner (which would be highly unusual), or (c) modifies a corner and changes which other corners it's linked to (i.e., a mistake was made previously).

If the current record changes, the matching corners are also changed as necessary. Those that still match up to the modified corner will still be linked to the same note because the linking value in that parent table will also be updated. The ones that are now unmatched will no longer have any notes attached.

The only time there is an issue is if an unmatched corner now becomes matched and the original unmatched corner had a note attached - and I've handled that [with some difficulty] also. The routine allows the user to decide whether to combine, overwrite, or lose the old note. (This project has been fun, fun, fun!)

If I can safely use Ref. Int. in my set, everything else is solved. If not, then I need to expand the other routine to also update the notes table. I can do it but I'm hoping I don't have to.

Problem is, I haven't used R.I. in so long that I don't remember what the issues were and whether or not they would apply to a simple 1:1 set with no ordering or filtering involved. Also, I'm only presuming the issues still exist because I think I remember seeing comments about it recently. The app may be networked but the chances of two people editing related corners at the same time should be fairly remote.

[CALocklin]

Re: Referential integrity in 1:1 set.

I'm baffled Cal.
Can you let us know a bit more about the app?

Off the top of my head it looks like you will need a transaction table in the middle, bit like a Library Book app. ...

I had to think about that for awhile and try to understand why it wasn't done that way in the first place. The problem is that in this case the linking value for the parents can change and when one changes, all 'new' matching records, if any, change to the same thing and the 'old' matching values, if any, will also change but to something different. So far, all of that is completely unrelated to the child (notes) table and has already been handled with the complex script mentioned above.

If I added a transaction table to the mix, I would just have to modify the transaction table also but it wouldn't make the rest of it any easier and would probably make the overall change more difficult. The problem is that we are not only changing the linking value but also changing which parent records the one notes record is linked to. So, if there was a transaction table, both fields in the transaction table would have to be checked and modified as necessary along with the fields in the various parent tables - and the transaction table becomes superfluous. (wow! spelled it right the first time)

I'm glad you brought this up. It made me think about it more and now I'm comfortable that I made the right choice.

[MoGrace]

Re: Referential integrity in 1:1 set.

Hi Cal,
I am curious how this got resolved. Can you give us a screenshot of your form? And perhaps the set structure?

[CALocklin]

Re: Referential integrity in 1:1 set.

A screenshot or set structure wouldn't do you any good. It's a "simple" 1:1 set where one child record can be linked to multiple parent records. In other words, memo "A" can be linked to parent records 1, 32, 55, and 168. There are also some scanned documents that are linked in the same manner. (The linking value is based on the list of matching corners.)

I'm still working on all the "change" implications. (In fact, I just came back here to work on it when I saw the e-mail about this.) I thought I had it pretty well taken care of a month ago but the more I get into it, the more complex I realize it is. The memo by itself wasn't too bad. The fact that there are also scanned documents that use the same basic link have made it 4 times more difficult.

If the parent gets changed so that only records 32 and 168 are matching, then somehow I have to let the user decide what to do with the memo and whether to change the scanned documents to link with records 1 and 55 or 32 and 168. The scanned documents would almost always go one way or the other. However, the memo could get copied, moved, or left "as-is". Unfortunately, "as-is" isn't really as-is - see the part about "in either case" in the "I'm confident" paragraph below.

This is a real challenge and the only solution seems to be some very complex xbasic with user prompts which will probably include an option for the user to view the scanned documents before deciding what to do with them.

I'm confident I can get this worked out but I may have to give up on the use of referential integrity. Tom was right that it would break the link to the other parent records and we originally thought that would be acceptable - and even desirable. However, we later realized that sometimes the memo will need to go with the "old" (unchanged) records AND in either case, even the old "unchanged" records are not really unchanged. The linking value that was "Corner1,Corner2,Corner3,Corner4" when all 4 corners matched could become, for example, "Corner1,Corner3" for the "changed" record; "Corner1,Corner3" for the one "unchanged" record that is still matched to the "changed" record; and "Corner2,Corner4" for the other two "unchanged" records. So, in this case the term "changed" only refers to the values that define the corner itself. No matter what happens, the linking value will change on all potential matches whenever any one of them is linked/unlinked from the group.

Then there's the other factor - sometime nothing will change except the matches. It's possible that someone will discover that although Corners 1, 2, 3, and 4 are currently listed as matching, they really aren't and the matches have to be corrected. So, even though nothing else changes, we may still have to change the links and the scanned document names.

Are you confused yet? If not, rest assured - I am! I've warned my wife not to worry if she sees me with a wet towel on my head - I'm just trying to keep by brain from overheating.

[MoGrace]

Re: Referential integrity in 1:1 set.

Cal,
Can the 4 corners each have different notes? Cause it sounds like regardless of which corner is being edited it ought to still link to the one note that was created for which ever corner was first created. And if the corners relate to a section, then why is the note not linked to the section so it is available to all corners?

This is what your set sounds like it ought to look like to me:

Section_HDR
===>Corners
---->Note
===>docs

[CALocklin]

Re: Referential integrity in 1:1 set.

An outside corner could normally match 3 or 1 "external" corners if it was the outer corner of the township. (3 is max in this case)
An outside corner could normally match 2 or 0 "external" corners if it was the corner of a section that was on the outer edge of the township but not the corner of the township itself. (2 is max in this case)

In some cases, it could be anywhere from 0 to the max number if previous surveys were not very accurate - which happens more often than I realized.

The memo will always be common to all matching corners.

So the "link" is more like this (if I can get it to look right):
Well, that didn't work - see attachment.
Corner_links.png

The docs are "linked" just like the memo except they aren't in the database itself. They're simply named using the linking value. At this time we are only allowing one document per match group.

There is no separate "Section Header". I thought about that but didn't see any advantage to it. In fact, it made it more complicated.

The trick comes when a matched set of corners changes for any reason. Then it's an issue of deciding which section records to change and what to do with the memos and attached documents.

And, to make things worse, I'm only discussing the external corners here. In actual practice I need to check the internal corners as well. Normally they should always match and there should be no changes. But I know that users can always find a way to mess up data so I need to have a "check" routine for those as well - just in case.

To look at this another way, the user might type in Town 19N Range 16E Section 32 and add the memo and a scanned document. The system would create the matching corners if they don't exist. Later on they come to check one of the matching corners and discover that the documents and memos are for section 23 not 32. In this case, the section number changes and all matching corners also have to change. This changes the linking value which means the document name has to change also.

The obvious question in the above case is, "Why not use a more generic ID so it wouldn't have to be changed?" Well, that would be great except for two things:
1. The customer wants the document names to be "readable" by themselves which basically means using the corner names as the document name.
2. When matches get changed, a new linking value will usually be required (assuming the change splits them into separate groups) but in some cases the linking values will be commonized if mismatched groups are now determined to be matching. This can be done with generic linking values but it's a lot easier to verify things when the linking value is meaningful.

Note: Having a "meaningful linking value" is not something I have ever considered important. In fact, I usually recommend against it. However, in this particular case I think it has a lot of advantages.

As I think I mentioned above, I've given up on the idea of using referential integrity. There are too many situations where it won't do what's needed.

[Ted Giles]

Re: Referential integrity in 1:1 set.

Cal, I have lost the plot entirely now.

You must be a glutton for punishment.

Just a comment though.

I use a field which holds a textual ALIAS for Hyperlinks and WebLinks in one app.
The reason for this is that a file hyperlink might be

J/SysServ/BST/Projects/Dios/Planning/MSP-Plans/MSP2003/Dios Plan 010199.msp
So the Alias is "Latest Plan"

Same with Weblinks.
http://finance.yahoo.com/q?d=t&s=ORCL
Alias = "Oracle Nasdaq"

Users always forget where they squirrel things away, and this is a simplified way of jogging theri memory.

[CALocklin]

Re: Referential integrity in 1:1 set.

I certainly feel like a glutton for punishment right now. Let's just say it has been an interesting challenge.

Most of the time I just "write code" because I have a pretty good idea already in my head about what needs to be done. Sometimes I write a short outline first - typically 5-15 lines - just so I don't get focused on one part and forget something else.

This time I finally had to give up and write an outline first because I just could not get the logic straight without writing it down. The outline for entering new records is hopefully complete - it's about 60 lines long. The "change" routine will be even worse.

[MoGrace]

Re: Referential integrity in 1:1 set.

Cal,
I am assuming that any scanned documents will be in PDF format? Since your users want to search for a file name independently, it still makes sense to give them a PDF viewer in the app because searching for their files can be controlled by searching in a form or browse. And if your table has a unique ID field, a document name and a hyperlink to the file, couldn't a file be renamed when necessary, by first changing the document name and confirming the change before renaming the file? Then linking the corners to the correct document and memo could be done by using the ID field for the link, since that will never change.

I wish I could see the project...