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
  • 704,970
  • 168
  • 1,303
  • 1,425
HashtagForgotName
  • 629
  • 1
  • 7
  • 19
  • 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
  • 31,930
  • 6
  • 24
  • 40