1

So the question what is better to use a linked tables vs json objects in a mysql database for many to many relationship so for example you have a user table like this

 id
 name
 email

and event table:

 id
 name
 description

The question is if its better to add a extra text field toward the user table with text column where you store a json object where you put event ids in like this

user table:

id
name
email
json

with the json object looking something like this

{
   {event_id: 1},
   {event_id: 2},
   etc
}

or have a linked table with 

id
event_id
user_id

assuming you have a many to many relationship where one user can register for multiple events and one event can have multiple users. where you also want to count how much users belong to 1 event and also wanna ask which one is optimal to use for querying and performances while doing this in laravel.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
HashtagForgotName
  • 651
  • 1
  • 7
  • 23
  • 1
    No variants - linked table only. – Akina Apr 17 '20 at 12:16
  • For sure linked table is a better idea than the JSON field(if we're talking about relational database). Many databases don't support natively indexing on JSON fields, so your queries will be really slow when you have a lot of data. – Daniel Petrovaliev Apr 17 '20 at 12:16

1 Answers1

1

Your linked table has all the information 

SELECT COUNT(*) FROM linked_table GROUP BY event_id WHERE event_id = 10

So you now have the number of users that belong to one event.

even with mysql 8 you have to put much more work and code to get information.

Edit:

besides json are slightly better that comma separated field Is storing a delimited list in a database column really that bad?

nbk
  • 45,398
  • 8
  • 30
  • 47