Jump to content

  •  

Photo

Join table inverse relationships


  • Please log in to reply
22 replies to this topic

#1 eJK  novice

eJK
  • Members
  • 21 posts
  • :

Posted 11 November 2005 - 12:53 PM

Hi,

I'm working on a knowledge base / help system. It contains articles. I'd like to create links from one article to another as related articles, which show up in a portal.

Right now I'm doing this with a Join table that contains a pair of ArticleNumbers, one for each of the related articles. This relates to a second Article table occurence. So I can easily link Article 2 to Article 3, which is set up as an entry in the Join table.

The problem is this: I'd like the inverse links to automatically get set up, i.e. if article 2 links to article 3, article 3 should also link to article 2. Right now, they are just one-directional. Do I have to have pairs of links in my join table, one for each direction? Or is there a more elegant way to do this that I'm missing?

I'm sure this has been addressed somewhere else, but couldn't figure out good search terms for it, so a link to another thread would be great if you know of one.

Thanks for the help.
  • 0

#2 Ender  A Space Oddity

Ender
  • Members
  • 4,780 posts
  • :

Posted 11 November 2005 - 03:02 PM

Hi eJK,

I have attempted this puzzling problem in the past, and I'm pretty sure that you will need to create two join records: one for each direction. This can be scripted, and if you use a script to create both, then it's pretty problem free. You can check out my demo in this thread on a similar subject:

http://fmforums.com/...php?tid/135019/
  • 0

#3 eJK  novice

eJK
  • Members
  • 21 posts
  • :

Posted 11 November 2005 - 03:07 PM

Thanks Ender,

I knew some of you experts out there would have encountered this situation.

As you realize, the problem with scripting is that it takes away the ability to create a new relationship from directly inside the portal. Also, deleting one relationship doesn't delete the second, so deleting them would also have to be scripted somehow.

Guess I'll just have to live with it. Thanks for the feedback!
  • 0

#4 SlimJim  gaudeamus

SlimJim
  • Members
  • 540 posts
  • FM Application:8 Advance
  • :

Posted 11 November 2005 - 03:21 PM

This is an interesting theoretical problem. You are creating a symmetric relationship between a table and itself and hence to define the relationship you do not need the full join table of ordered pairs of IDs. The join is sufficiently defined by unordered pairs (sets of two IDs).

To put this into English do not use a pair of IDfields in the join table use only one IDfield and drop the pair of related ID's into it separated by a carriage return. Use this as the match field link between the two occurrences of the table.

One more thing you have to decide: do you want to relate an article to itself in this scenario. If you do then begin the join field IDs by copying all the ID's of all the records in the table.

EDIT: Sorry ejK I missed you second post while writing this. I think your two points are answered by using a single ID field.
FURTHER EDIT: I should make sure that you have read the first sentence. This is a theoretical solution. I have not at any time tried to do this and haven't really thought about it before this post. So no guarantees.
  • 0
Vita nostra brevis est

#5 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 19h 59m 17s

Posted 11 November 2005 - 03:29 PM

A simple solution would be to show 2 portals - one showing articles this article is pointing to, another showing articles pointing to this articles.

There could be a couple of ways to group both kinds together. Much depends on what this is going to be used for. It could be as simple as creating a concatenated field in the join table (= ArticleNumber1 & ¶ & ArticleNumber2), then filtering out the current record. Or you could use a relationship based on value lists, as shown in the attached file.

Attached Files


  • 0

#6 Ender  A Space Oddity

Ender
  • Members
  • 4,780 posts
  • :

Posted 11 November 2005 - 04:26 PM

Hey Slim,

Your theory sounds like a multi-key. And while I'm a big fan of multi-keys, I don't think they would work well here. The trouble is you should be able to create a join from any record to another and also remove a join from either record. I don't see an elegant way to do this with multi-keys. If I've missed something, perhaps you can try to get a sample to work and share it.
  • 0

#7 eJK  novice

eJK
  • Members
  • 21 posts
  • :

Posted 11 November 2005 - 04:41 PM

Hmmm, SlimJim's concept is interesting... it kinda sorta works.

I set up an additional Calculation field in the Join table, that concatenates the two Article numbers with a Paragraph character in between. This works for pulling in the link to the portal for each records.

The new problem is this: I can't find a way to determine which of the two to display in the portal. Or rather, I always display one of the two, which is the same one for each record. In other words, if 2 is linked to 3, in 2 the linked record shows up as 3, and in 3 the linked record also shows up as 3.

So this is going to take some extra finessing. I'm trying to figure out a way that the related record can have a calculated value based on the record it's related to. Will keep you all updated if I figure something out.
  • 0

#8 Ender  A Space Oddity

Ender
  • Members
  • 4,780 posts
  • :

Posted 11 November 2005 - 04:43 PM

Hi comment,

Looks pretty good. Although the process seems a little awkward to add records through a different portal than where the All appear.
  • 0

#9 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 19h 59m 17s

Posted 11 November 2005 - 05:01 PM

Maybe. It's more of an interface issue, and I have no information on workflow, so it could be solved in any number of ways. In a way, it makes sense to me that you cannot enter links TO this record - since this information is not available on this record.

I must say I am quite puzzled regarding the need for this: I believe an article should list its own sources, and COULD (in a way of boasting) mention other articles where this article was sourced. These two seem quite separate to me.
  • 0

#10 SlimJim  gaudeamus

SlimJim
  • Members
  • 540 posts
  • FM Application:8 Advance
  • :

Posted 12 November 2005 - 02:07 AM

Ender

Yes it is a multi-key and as I said a theoretical solution. I don't have your experience at knowing what is and is not possible. Be that as it may I felt obliged to have a go at implementing it. Adding keys is simple enough (but I haven't implemented any checks for uniqueness), deleting keys is also simple but choosing the right one is not so simple!! As eJK as pointed out displaying is a problem also. It can be done to some extent. I am posting my effort but this is not to be regarded as a solution and it is definitely not pretty.

To move on and following on comments last post. I think another possible approach to the problem might be to use categories (and sub-categories etc if you wish). This has the advantage that data entry classifying an article can be done without a view of all the other articles. When you want to see related articles you use a simple self-join by category portal which requires no join table.

Attached Files


  • 0
Vita nostra brevis est

#11 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 19h 59m 17s

Posted 12 November 2005 - 03:49 AM

It could be as simple as creating a concatenated field in the join table (= ArticleNumber1 & ¶ & ArticleNumber2), then filtering out the current record.


Your file does the one, but not the other: see, for instance, Article 8.
  • 0

#12 SlimJim  gaudeamus

SlimJim
  • Members
  • 540 posts
  • FM Application:8 Advance
  • :

Posted 12 November 2005 - 04:39 AM

Yes I have since looked at your posted file and made some alterations to enable the filtering. Refreshing seems to be a major issue. I decided to use an auto-enter for the link list, since both creating and deleting joins is scripted I could force a refresh, But refreshing after a delete didn't work. The script shows what I had to do to force a refresh. I feel that this should not be necessary.

OK. Here is the new version - it works somewhat better as a result of amalgaating comments ideas but the interface is still very sparse.

Attached Files


  • 0
Vita nostra brevis est

#13 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 19h 59m 17s

Posted 12 November 2005 - 05:26 AM

Refreshing is always an issue when a relationship is based on a key derived from another relationship. I find that using unstored calcs and Refresh Window [Flush cache..] works adequately - and the refresh can be incorporated into navigation.
  • 0

#14 SlimJim  gaudeamus

SlimJim
  • Members
  • 540 posts
  • FM Application:8 Advance
  • :

Posted 12 November 2005 - 10:15 AM

Refreshing is always an issue when a relationship is based on a key derived from another relationship. I find that using unstored calcs and Refresh Window [Flush cache..] works adequately - and the refresh can be incorporated into navigation.

Right as usual. I have changed the linked field to an unstored calculation, Thrown some scripted refreshing into the create and delete join entries and that seems to be working fine. With a reasonable number of articles the list of unlinked articles is goin to get unmanageably long so I have thrown in an extra Category filter on the unlinked portal, and finally chucked in a bit of navigation.

I have left the refresh button in but I have a feeling that it is no longer necessary. I have bored you enough with this. As I said at the beginning it was a theoretical solution but I do think that this at least shows that it is possible to produce a symmetric self-join using only two TOs - the table and the join table. The remaining TOs are to do with implementation of the data entry not with the underlying structure.

Attached Files


  • 0
Vita nostra brevis est

#15 eJK  novice

eJK
  • Members
  • 21 posts
  • :

Posted 14 November 2005 - 01:10 PM

Great work guys! Got it running, works just as advertised. Can't add relationships through the portal, but that's well worth the functionality. Thanks a lot for the examples.
  • 0

#16 T-Square  Datatrooper

T-Square
  • Members
  • 792 posts
  • FM Application:10 Advance
  • Time Online: 1h 19m 22s

Posted 16 November 2005 - 01:12 PM

I am jumping in at the tail end of this discussion because my primary educational and professional background is as a librarian and information scientist.

What you all are discussing is creating a syndetic structure--a set of links that join different concepts in a knowledge base. Basically, there 3 types of link in such a structure: Broader terms (BT), Narrower terms (NT), and Related terms (RT). The BT/NT links are two sides of the same link, while a related term is, as SlimJim says, symmetric.

Comment's solution implies a BT/NT relationship, since each portal shows one side of the relationship. That doesn't sound like eJK's situation.

It would seem to me that the metaphysically cleanest approach would be to script creating reciprocal RT link records (ID1/ID2 ID2/ID1) in the join file, and identify that the link is type RT. Then, you can also use this file to store BT/NT entries.

Cheers,
David
  • 0

#17 Ender  A Space Oddity

Ender
  • Members
  • 4,780 posts
  • :

Posted 16 November 2005 - 02:01 PM

I'm not familiar with your terms, but you're right that there are two basic options here; Either one join record, which has no directionality implied, or two join records, where each can remember different information about the relationship in each direction. On the referred thread in my original response, it was important to remember different information in each side of relationship. It sounds like this is the case for your metaphysical thing too. It's still not clear to me which structure eJK needed (or used.)
  • 0

#18 eJK  novice

eJK
  • Members
  • 21 posts
  • :

Posted 16 November 2005 - 02:09 PM

I wanted a aymmetrical, one-to-one relationship, or what T-Square calls an RT link.

I agree one solution is to use double (inverse) entries in the link table, but then you have to deal with deleting (and creating) both of these entries, which didn't seem as straight forward as just listing the entries once. Which is what I got in the end, thanks to you're guys' help.

I like the cool little trick of using the Value List of related records from the Links Table to fill in the Links Field in the Articles Table. Very nice, never thought of that. Everything is working great now. Thanks again for all the feedback.
  • 0

#19 SlimJim  gaudeamus

SlimJim
  • Members
  • 540 posts
  • FM Application:8 Advance
  • :

Posted 16 November 2005 - 05:13 PM

I haven't come across the notion of a syndetic structure so naturally I did a Google search and found less than 13,000 hits and the number of hits didn't change by removing ebay etc. So it would appear that you can't buy a syndetic structure on eBay! Many of the articles seem rather technical or somewhat vague and at this point something in the middle would be interesting.

I suspect like most people interested in computing I have my own knowledge base of articles (my classification is rather primitive with a simple three levels of categories) Would a syndetic structure help here? Is there any simple implementation of it which would make it easier to hone down onto appropriate articles, or are all the methods available more suitable for the British Library or Library of Congress.
  • 0
Vita nostra brevis est

#20 T-Square  Datatrooper

T-Square
  • Members
  • 792 posts
  • FM Application:10 Advance
  • Time Online: 1h 19m 22s

Posted 16 November 2005 - 06:26 PM

eJK--

If you script the creation of the reciprocal records, then removal *should* be manageable using relationship definitions--specifically, if you create relationships between:
1) ArticleID and JoinIDA, and
2) ArticleID and JoinIDB

and set each to delete the linked records in JoinTable when a record in Articles is deleted, you have no further delete maintenance.

SlimJim: take a look at the following:
http://instruct.uwo....saur/main00.htm - for an overview of thesaurus construction, and
http://www.archivist...initionKey=1165 - for a definition of syndetic structure as I am using it here.

With regard to small knowledge collections, the best solution is your own brain. The main reason to use a classification scheme is that your collection is so big that you don't have any way to figure out what's in it (or where it is). You might check out the following for a discussion of the issues:
http://www.bayside-i...stead/about.htm
  • 0




FMForum Advertisers