I have a products table, which is displayed in a tab as a list. In the details page for each product, I want to show the details of any campaigns the product has been a part of. In order to keep my data properly normalized, I have organized the data into three relevant tables:
the products table, which has a multuple relation column mapping it to a campaign-products table (each product can be a part of multuple campaigns)
a campaign-products table that maps product ids to campaign ids (defining the membership of products in campaigns) , and has a single relation column mapping to the campaigns table
a campaigns table with the details for each campaign (with campaign_id column as the unique identifier)
Again, I want the campaign details to be accessible in an inline list within the product details page. How would I do this while keeping my data normalized? It seems like what is required is two joins. Or is there some other way to say (for this product, get all the campaign ids that match the product id in the campaign-products table, and then get all of the campaigns referenced by those campaign ids, and select the campaign_name from that campaign details record, for example)?
Even when I click on the specific item in the inline list, which should now bring me to a details page for the product-campaign record, which should include a single relation column relating to the campaign details table, I do not see the said relation as a possible data source for some reason.
so, you have 2 tables… products and campaigns… you can relate them both ways by having joined row IDs column for each table…
every time you add a product to a campaign… add the product row ID to the joined text… do the same for the product. now split this value, and you get the base for multiple relations…
to remove ID is tricky… and hard to explain in short way
To clarify, I actually have 3 tables. A products table, a campaign-products table, and a campaigns table. The campaign-products table has the sole purpose of managing the mapping between the products and campaigns.
But it sounds like what you’re suggesting is that I only have 2 tables, and to handle the mapping from campaigns to products, rather than a relations column in the campaigns table, I have a different type of column that is somehow storing a list that I can append to or edit. What is the exact column type I would be using here? Is it a joined list? And how would I add a new value to the list every time a product is added to a campaign? In my previous plan, a new product would be added to a campaign by adding a record to the product-campaign table. But now it seems I would be editing a field to append an item to it manually? Seems like a sub-optimal UX…
There’s two ways to do this. Like what was mention above, my preferred method would also be to only have 2 tables. The Products table and the Campaigns table. It depends on your app flow, but you could have a comma delimited list of Campaign ID’s in the Products table, OR have a comma delimited list of Product ID’s in the Campaign table. There are a lot of ways to create that comma delimited list. Then all you need is a Split Text column to split the comma delimited list into an array. Then you can create a relation From or To that array, as needed.
Now, if you want to stick with your existing method of 3 tables, you are going to end up using a lot more rows to do the same thing. But, to get what you want, what you do is create a relation from the Campaigns table (or Products table if you are going that direction) to the Campaign-Products table. Then create a Lookup column to get an array of Product ID’s. So, for example, if you create a relation in the Campaigns table to your campaign-products table, then your lookup would return an array of Product ID’s from the relation. Using that lookup array, you then create another relation to the Products table. That second relation will contain all products in the matching campaign. So, it’s ultimately a Relation/Lookup/Relation combo to create the link from the Campaigns table to the Products table through the Campaign-Products table.
try to eliminate the third table, it looks like is just a version of what you have… use filters to achieve the desired look… @Jeff_Hager nicely explained how to do it… I can add only one more method, which is using the template column… have a text of coma joined IDs + , “new ID”
to delete ID… substitute “, ID” with empty space… for the first ID, create a special logic, because it will not start with coma
