Laravel Many to Many Relationship : Pivot VS JSON

Question

i wanted to get your expert opinion about this dilema chosing bewteen JSON or Pivot Table

Let just say we have 2 tables here

1. people
2. jobs

A person may have multiple jobs, alas, a jobs might have multiple person subscirbed to it. What is the best approach to it?

Method 1: JSON

I would have jobs column in people table, that contain json array of that person's jobs id, example : [1,2,4]

Method 2: Pivot

I would create pivot table job_person with job_id and person_id column, well, you know Laravel Eloquent style many to many pivot table

I have done some searching, and i found articels favouring each method, some say JSON better because it musch simpler, others would say Pivot is better due to that is how relationship database should work, etc etc.

But i want to know, which one should i use in what scenario? Like if it is just simple case like above scenario, JSON would be better?

What if there are other variables included like additional pivot columns (Maybe each pivot also contain status column that can be set to active or past_job)

Or what if in the future we want to be able to get all peoples whom have a specific jobs, in which case Pivot would be preferable i think.

What if instead of jobs, the other table would be books and a person can have an extensive of books making we might have tens, or even hundreed pivot records just for one person? And there will be another hundreed persons?

What if instead of books, the other table were stocks in which case, a person might subscribed / unsubscribed multiple stock multiple times?

And maybe to the basic principle, what is each one's advantages/disadvantages?

Thank you very much

Answer 1

I would rather not choose JSON, as there's no benefit from choosing it, you will sacrifice many of the database features and make querying the data difficult and slow.

What if there are other variables included like additional pivot columns (Maybe each pivot also contain status column that can be set to active or past_job)

Job and Person are not dependent on each others, so you need to create an associative table between them something like "PersonJob" and add necessary information to it, this is easy to traverse in Laravel.

Or what if in the future we want to be able to get all peoples whom have a specific jobs, in which case Pivot would be preferable i think.

You could easily query this using the associative table.

And maybe to the basic principle, what is each one's advantages/disadvantages?

it just that relational databases are made for this kind of stuff and JSON offer no value just hardship.