many-to-many relationships

[hassam36]

Hello

Concerning many-to-many relationships, I understand that to create this type of table relationship an intermediary table is needed containing the primary key fields of the two tables in the many-to-many relationship (see below).

firstTable:

primaryKey1

field1

field2

intermediaryTable:

primaryKey2

primaryKey1

primaryKey3

secondTable:

primaryKey3

field1

field2

My question is how does a record created in the second table get associated with a record from the first table using the intermediary table? Does the user have to manually create this?

Paul

[ssaucedo]

There is an example at:

http://courses.mbl.edu/Medical_Informatics/2000.2/Remsen/Databases/Many2Many.html

Hope it helps.

[bruceR]

Hello

Concerning many-to-many relationships, I understand that to create this type of table relationship an intermediary table is needed containing the primary key fields of the two tables in the many-to-many relationship (see below).

firstTable:

primaryKey1

field1

field2

intermediaryTable:

primaryKey2

primaryKey1

primaryKey3

secondTable:

primaryKey3

field1

field2

My question is how does a record created in the second table get associated with a record from the first table using the intermediary table? Does the user have to manually create this?

Paul

First of all it would be more like this, using your notation.

intermediaryTable:

foreignKey1

foreignKey3

primaryKey2

Secondly, it generally helps to give more information including a realistic description of what you are doing and also think about how the user actually uses a system. Are you enrolling students in classes? Renting property? In general you will select record in the primary table, such as people; and some kind of chooser list to select the second records. For instance you would be on a student record and see a filtered portal of classes by type (English, History, etc). When you make the selection of the class ID then you create the join record holding studentID and classID.

Edited November 30, 2007 by Guest

[hassam36]

Bruce

In my situation, I have a table containing budget allocation line items (i.e. $1000 granted by State Agency X for Red Cross) which must be related to another table for Encumbering each line item. Each line item can be associated to more than one encumbrance event and one encumbrance event can contain more than one line item.

The issue is that that the tables are not populated mutually exclusively: I would never create an encumbrance event by itself (without any corresponding budget line items). From a business standpoint, the encumbrance table should be a child table to the budget line item table.

Regardless, in your example, when you are on the student record and you select the class ID, I'm assuming you are populating this class ID in the intermediary table?

Paul

[hassam36]

Ssaucedo

Actually, my issue is more related to how data from each table gets most easily linked using the intermediary table in the actual Filemaker form, not as much how to set up the physical table structure.

[Fenton]

I'll answer for Bruce; yes, in his example you would be choosing the ClassID, as a foreign key, IN the join table.

As you say, once you understand how to structure a join table, with its foreign ID keys and relationships, the question then is "how to populate them." Bruce mentions a "filtered portal." If you have a lot of records, this is often the best solution. It is the most flexible; often the fastest. But it requires some interface work.

If you have only a few records to choose from, then a drop-down list is sufficient. This works best with smallish numbers, and with [x] Allow creation of related records portal relationships.

That is why he asked what exactly you're doing. It is impossible to suggest the best method without know what (How many rows expected in the portal? Is the portal sorted? What are the "filter" possibilities?), and approx. how many items to choose from (tens? tens of thousands?). Even with this info it's often hard to say what's "best."

[Kball1]

I have a similar many to many relationship question. I have one table of 25 different price groups. I have another table of 1,000 part numbers. In this case 1 price group can have many part numbers and 1 part number can have many price groups. My intermediate table has price group and part number. How can I effectively assign my part numbers to their appropriate price groups without having to manually assign each one?

Thanks

Kurt

[Søren Dyhr]

How can I effectively assign my part numbers to their appropriate price groups without having to manually assign each one?

This is probably a spot where the ommision of a dedicated table for the joins, where a pilcrow delimited list in a text field is enough. The trial to make is if statistic's need to get done would the pilcrowed list not be sufficient.

--sd

[Fenton]

I would think that a join table would still be best for this. Because you could put a portal on the Part layout to the join table, with [x] Allow creation of related records. Then you could add groups to a part via drop-down value list of Groups. A multi-line list would expect you to remember the Group IDs (which maybe you do). It sort of depends on your skill when dealing with multi-line fields (in case you decide to delete a group, merge 2 groups; actually tricky no matter which method you use). 6 of one, half dozen of the other :-]

As far as mass assigning a group to parts, the question is, how would it know which parts? If there are Finds you can do on Parts, to produce a found set of parts that all need a specific group added, then yes, you could add it in one operation.

In the case of a join table, you would Import those part IDs into the join table. Then Replace the Group ID into the join table found set. Or, an alternate method is to set a global field in Parts to the Group ID, then Import that with the Part IDs (to do the whole thing in one operation).

I'd highly recommend that you either Save a Copy As, or Export the data from the join table between operations, so that if something goes wrong you don't have to start all over again.

[Agnes Riley]

Hello All,

I have a somewhat related question. I have tags and documents that need to be tagged (for later use on a website).

I have 3 tables:

Documents (ID, etc.)

Tags (TagID, tag)

DocumentTag (Document ID, TagID)

On a layout from the Documents table I have a portal showing all the possible tags from the Tags table; based on a constant relationship. Right now I have a plus sign on the left of every tag and a delete sign to the right. It's redundant. I have another solution where I was able to show red or green arrows based on whether the current stock price was lower or higher than the closing price. That was easy, because the information was in the same table.

Here every time I add a tag to a document a record gets created in the DocumentTag table with the serial number of the tag and the document.

What I'd like is an "add button" to show if the tag hasn't been added to this document and a "delete button" if it has. I have two graphics in two global container fields. I cannot figure out for the life of me what criteria I should base my calculation on to show the right graphic in a calculation container field.

Any ideas anyone?

Thanks,

Agi