0

I'm having a many to many relation. Lets say that I have Many users that can be in Many groups and the other way around.

I wan't to make a table that contains the following columns

group_user

id / name / user_id / group_id

Since I have only 3 groups Junior, Middle and Senior I don't want to make another separated table for them. My idea here is to make the following approach:

  1. Creating 3 records in the same table group_user with the following data

id / name / user_id / group_id

1 / Junior / null / null

2 / Middle / null / null

3 / Senior / null / null

So now when I want to insert a group_id I will use this 3 ID's that I just created with NULL user_id and group_id.

My records will look something like this:

id / name / user_id / group_id

4 / NULL / 125 / 1 -> The id of the Junior group that is in the same table.

5 / NULL / 125 / 3 -> The id of the Senior group that is in the same table.

Is this a valid way to do it? How wrong it is?
Kristian Vasilev
  • 504
  • 8
  • 26
  • Create a separate table for the metadata of the 3 groups because what you are doing here contains inapplicable nulls which are not allowed in a normalised relational database. (Inapplicable NULL is a null that can never have a value under any circumstances) – apokryfos Mar 27 '19 at 08:46
  • This makes sense! Thanks a lot I will read more for "Inapplicable NULL", thanks. – Kristian Vasilev Mar 27 '19 at 08:51

1 Answers1

0

I would recommend following the correct procedures as follows:

Table 1 users table: id | name

Table 2 groups table: id | name

Table 3 group_user pivot table: id | user_id | group_id

Note: Table 3 should never hold a nullable value

The relation would be as follows, right now we have two models, a User model and a Group model.

both will have a belongsToMany relation towards each other.

Here is the method you will use in the User model:

public function groups()
{
    $this->belongsToMany(User::class);
}

And here is the method you will use in the Group model:

public function users()
{
    $this->belongsToMany(Group::class);
}

This is the recommended way to continue.

vahan terzibashian
  • 258
  • 1
  • 4
  • 9
  • I will definitely take this way. I was thinking that It's stupid to have a table with 3 records in it. But it's even worst to have a table with denormalizated data in the table. Thanks a lot for the answer and the spend time to write it :) – Kristian Vasilev Mar 27 '19 at 09:01
  • no no, it wasn't stupid of you to think of it that way, I know it doesn't make much sense, but you will see that this will flow very naturally inside your application. Update me on how it goes. – vahan terzibashian Mar 27 '19 at 09:06