Relationship between 3 tables

Question

I have a mysql DB with 3 tables:

- Campaigns
 id
 code
 name

- Columns
 id
 name

- Values
 id
 idColumn
 value

The relationship beetween Campaigns and Columns is many to many:

- ColumnsCampaign
 id
 idColumn
 idCampaign

and the relationship beetween Columns and Values is one to many.

I have this query that returns all values for each campaign:

return Campaigns::addSelect('id','code','name')
->whereHas('typeCampaign', function ($q) use ($idTypeCampaign,$idMenu)
{
    $q->where('typeCampaign.id',$idTypeCampaign)
        ->whereHas('menus', function ($q) use ($idMenu){
             $q->where('menus.id',$idMenu);
        });
})//this part define which campaign i want with a certain menu
->with('columns.values')//with this i include the campaign columns and its values
->get();

My problem is that if i have different values for the same column i dont know to which campaign is associated. How can i solve this issue? Is it correct to create a relationship table between campaigns and values?

Answer 1

This isn't (yet?) an answer but it might be a little more obvious to the rest of us if the structure was more like this:

Campaigns
campaign_id *
code
name

Columns
column_id *
name

campaigns_columns
Campaign_id *
Column_id *

* = (component of) PRIMARY KEY

Answer 2

It seems that the structure of the designed tables is not matching to its underlying domain.

If mutliple values can be assigned to a given column id (idColumn), then either an extra key field (which is actually a campaignId) is required to be introduced in table Values or create an extra table that associates campaig id, columns id and values id. The third option -this is what I recommned actually - is to unify the table Columns and Values.