Similar records

[peaceandplenty]

I'm in the design phase of a database and have a question. I'm not sure if portals are the answer so this might be in the wrong place.

My situation is similar to a dictionary: some of the larger dictionaries will have a discussion attached to a word definition, showing how a word and its similar words vary and how to use them in different situations. Example: take the words 'strong', 'secure', and 'impregnable'. Each of these has a record that defines the word. But each record also includes a discussion of the differences among these words and how you would use them in different connotations.

How would an FM layout be constructed so that the discussion (which includes all of the similar words) would automatically appear in the record for each of the words?

example:

Record 1: Strong: noun, able to move things; [here insert the discussion of all three words]

Record 2: Secure: noun, difficult to break into, as a bank; [here insert the discussion of all three words]

Record 3: Impregnable: noun, etc.; [here insert the discussion of all three words]

The discussion could be edited from any related record and would of course be updated wherever it appears.

I'm stumped.

[Matthew F]

It sounds like you want to create two related tables. One table is for the primary records (Record 1, Record 2, Record 3, etc.). The next table is for discussions. The two tables would be linked in a one-to-many relationship, such that the same discussion can be seen on multiple different "Records". A discussion ID number field should be defined on each table to be able to link the relationships.

The tricky part will be to figure out how the user will indicate that the same discussion should appear on multiple records. If they manually enter the discussion ID# then the design is easy. But that may be asking a lot from the user, and you may need to create some sort of search procedure.

Assuming that you have all this worked out, then perhaps you're still wondering about the Layout itself? Is the is issue that you don't want two separate fields, but instead you want both fields merged into a single paragraph of text? In that case, I think you will want to have a separate "edit" and "view" layouts. The view layout would combine the primary definition and the discussion through a calculated field or a Merge Field. In the "Edit" layout, these would be kept separate and would allow text entry.

[comment]

But each record also includes a discussion of the differences among these words and how you would use them in different connotations.

Can we safely assume that each word is discussed in one discussion only?

[peaceandplenty]

Mathew says: "It sounds like you want to create two related tables."

I know it does. I would make Table A for Definitions and this is obvious. Fields would be Word1/Definition2, Word2/Definition2, etc. This part is really simple.

"The two tables would be linked in a one-to-many relationship, such that the same discussion can be seen on multiple different "Records". "

Yes, but I keep asking myself: 'What exactly is a record in Table B'? Would it be like this: Record 1 is a text discussion of Word 46, Word 78, and Word 98? And Record 2 is a text discussion of Word 3, Word 26, and Word 108? Not very elegant and hard to imagine how links and relationships might work.

This doesn't seem to get me anywhere so I go back and ask: what are you trying to achieve? And the answer is this: If there are words in Table A with similar meanings, I want to discuss those similarities, and I want the discussion to appear on the layout with each word being discussed. This discussion isn't something that the user need search. It would simply appear only if the found set includes a word that has an expanded discussion.

By way of example then if I search for a word that has no expanded relationship to other words then this discussion wouldn't appear. I think I am missing something big here.

Forgive me for thinking this through out loud.

Comment asks: "Can we safely assume that each word is discussed in one discussion only?" And I think at this point no, we can't assume that just because it's not clear to me yet how to relate the discussions with the definitions.

[comment]

Comment asks: "Can we safely assume that each word is discussed in one discussion only?" And I think at this point no, we can't assume that just because it's not clear to me yet how to relate the discussions with the definitions.

I am afraid you are putting the cart before the horse. The question "how to relate the discussions with the definitions" is a technical one. My question is concerned with the real-life relationship between the two. We need to know what exactly are we dealing with, before we can answer how.

[peaceandplenty]

Carts, horses…I am just looking for up and down!

Here's a sketch of how I am envisioning things. Each box represents a record. You'll see two fields: 'Word', and 'Def'; I was pretty loose with these examples.

The red boxes and the purple boxes have an additional item. This is because the three red records contain words that are similar and the user needs to know how they are different/similar. Same with the purple boxes.

This is exactly how a modern dictionary works. In fact, the built-in Mac dictionary calls these 'word links:

Anyway this is what I want to achieve.

Hope this helps?

[qube99]

We do this sort of thing in ecommerce data. We will have a Product that has a number of attributes such as price, cost, weight, image, category, name, etc. We add another attribute we call Related Products. In this field we list all the related product codes, comma separated with no spaces.

The product display layout then has a script that reads the related product codes and shows them one by one on the product display layout.

You might look at a 10" Cast Iron Skillet and across the bottom you will see a row of Related Products. These might be Lids, Stainless Steel Skillets, Pans, etc. These all come from a single field in the product data containing a comma separated list of product codes.

In this method all the work is in data entry for the related products field. Someone has to create those lists. A single script reads the list and outputs the related displays.

It's all quite simple, no portals or extra tables needed.

We might run a conditional when related products field is empty. We would then run a Featured Products list or a Best Sellers list or a Newest Products list, all of these are auto-generated with no data entry.

It looks to me like your Related Words list has already been compiled. You just need to collect and format it for your display script.

[comment]

Carts, horses…I am just looking for up and down!

It's a simple question, I think, but rather crucial: can the same word appear in multiple discussions? Why is it so difficult to get an answer? If you can think of even one word that you may need to include in two discussions, say yes. Otherwise say no.

[efen]

Something like the attached? Only creates the links in one direction and there are probably better ways ...

dictionary.zip

[peaceandplenty]

efen, and Steve: thanks, I've been away a few days, don't confuse my tardiness for lack of interest. I'm looking at this tonight. And Steve: I hope you are right- the part about it being simple…I suppose I don't have my head wrapped around it yet.

[qube99]

Imagine you have a record for the word "house" that may have fields for pronunciation, definitions, etymology, discussion and the like. Add 1 more field for the synonyms. The synonym field will list all the words, which all have their own records, in a delimited list. The synonym field content for house might look like this pipe delimited list:

abode|domicile|gurt|mansion|tree house|lean-to|igloo

Now put a calculation in a display field that reads each synonym in the delimited list and displays it's related field information, concantenated and formatted in some manner.

You only need a self join table and 1 script to do the entire dictionary.

We read each character into a string until we hit a pipe. We keep looping while pipe = true, creating new strings. After the last character, pipe = false and we exit the loop. Within each loop we call up the related fields from each string and display them.

As I mentioned earlier, the real work is data entry for the synonyms. I don't know what your data source is, so maybe it can be scripted too. Perhaps you need a second script for data entry.

In both cases it's just a bunch of text manipulation. FMP is pretty good at this sort of thing.