One To Many (Inverse) or Many To Many?

Question

I am struggling between to database designs:

I have Table A (Metrics) where stored app metrics for different dates and from different networks.

id	app_id	network	date	param1	param2	...
1	A_123	A	2020-12-01
2	C_123	C	2020-12-01
3	B_123	B	2020-12-01
4	A_123	A	2020-12-02
5	A_123	A	2020-12-03
5	A_456	A	2020-12-01
5	A_456	A	2020-12-02
5	B_456	B	2020-12-02

A_123, B_123 and C_123 is same app, but comes from different network, that's why I want to create mappings to give a common name.

And I have Table B (Mapping table)

id	app_id	name
1	A_123	App Name 1
2	B_123	App Name 1
3	C_123	App Name 1
4	A_456	App Name 2
5	B_456	App Name 2

Now I have One To Many (Inverse):

public function mapping() {
    return $this->belongsTo('App\Models\Mapping', 'app_id', 'app_id');
}

but operations like update mapping data, seems a little difficult and not correct, because if I want to change mapping name and remove some app_id, I need to collect all TableB.id and pass it to script. And easier is just to delete([1,2,3]) and create 3 new records with new App name.

Then I thought that this is probably Many-To-Many, but seems not, because for example in Metric table for each app_id I have 10k records and if I want to map 4 id to 1 name, then for 1 mapping in table mapping_metric table would be 40k records.. and this seems is even worse then first design..

And my final thoughts, that if both methods are not working well, then probably my Metrics table not designed well or something else.

Do you have some ideas or you know some approach how can I map ids to name?

Answer 1

First for a good database design as our friend suggested your table have to be normalized so i suggest this illustration:

• A table name apps
• A table name networks
• A pivot table name app_table
• A table name metrics

and the relationship between them should be like:

• apps belongsToMany networks (pivot:app_table)

• networks belongsToMany apps (pivot:app_table)

• apps hasMany app_table

• app_table belongsTo apps

• networks hasMany app_table

• app_table belongsTo networks

• app_table hasMany metrics

• metrics belongsTo app_table

the only important thing is you have to make a model for your pivot itself

class App extends Model{

/**
      * retrive networks
      *
      * @return BelongsToMany networks
      */
     public function networks()
     {
         return $this->belongsToMany(App\Network::class, "app_table", "app_id", "network_id")
                        ->withPivot("deleted_at")
                        ->using(App\AppNetwork::class);
     }

/**
      * retrive app_networks
      *
      * @return hasMany [description]
      */
     public function appNetworks()
     {
         return $this->hasMany(App\AppNetwork::class, "app_id")->withTrashed();
     }

}

The Network Model Would be the Same with inverse relations.

but for pivot table model you have to extend from Illuminate\Database\Eloquent\Relations\Pivot and set public $incrementing = true; to increment id automatic

class AppNetwork extends Pivot
{

    public $incrementing = true;

public function network()
    {
        return $this->belongsTo(App\Network::class);
    }

    public function app()
    {
        return $this->belongsTo(App\App::class);
    }

    public function metrics()
    {
        return $this->hasMany(App\Metrics::class, "app_network_id");
    }

}

this were the primary relations that you can define in your for models.

for more complex relation you can define them using this tools:

• hasManyThrough
• eloquent join with getNamedAttribute or Laravel Scope
• BelongsToThrough.