Common field in many-to-many relationship
I have a many-to-many relationship as follows:
1) "Keywords" table recording keywords used for Google search
2) "Articles" table recording resulting Page One results from Google search
Keywords can have up to 10 result articles. A single Article can rank on Page One for multiple keywords.
I've set this up with a bridging table between the Keywords and Article tables. Sub-tables display correctly.
But... each Article has a unique attribute relating to each Keyword (a number field estimating how well the Article handles "search intent" for the Keyword).
How do I set this up to record and display?
Thanks for the help.
4 replies
-
How to set up? Since the "unique attribute" is a characteristic of the relation between the Keyword and the Article, it must be stored as a field in the bridging table.
How to display? It depends on where you want the display to appear. Let's say that you want it in the "Keywords" table. You don't mention the name of your bridging table, let's call it, well, "Bridge".
In the "Keywords" table form view, you should have a subtable (or view) called, by default, "Bridge". As columns of this table, you can add the "unique attribute", and any field you want from the "Articles" table. To do this, first tap the wrench on the top right to make it turn red, so your subsequent changes will hold. Then, tap on the header of the subtable, on the "Show column…" button, on the right arrow labeled "Articles", and finally on the name of a field from "Articles" you want to see in the subtable.
-
Thanks for the quick reply, Alain.
The problem is the Article table may have several Keywords in it. That is, a single Article shows up on Google Page One for more than one Keyword. So the Article table cannot have just one field for the unique attribute. In the example, the Article could rate "1" (low search intent match) on Keyword A, but "3" on Keyword B.
I'm not well versed on database structure, but I'm starting to think I need a third connected table in the Bridge table. It has just one number field in it, containing records 1,2 and 3 to capture the unique attribute. Sample results in the Bridge table:
- Keyword A, Article A, Attribute value 3
- Keyword B, Article A, Attribute value 1
- Keyword A, Article B, Attribute value 2
Is there another way to do it that doesn't involve a new table for a 3rd connection in the Bridge table?
-
That's why I suggested to put the attribute as a field inside the Bridge table. Nothing prevents you from adding data fields in the Bridge table in addition to the two reference fields, so you don't need yet another table.
-
Hey, Alain. That worked great! Thanks a lot.
Content aside
- 4 yrs agoLast active
- 4Replies
- 532Views