So i'm not the best with databases, what would be the best way then for me to accomplish this relationship? where the field

assigned_depts

is one or many departments?

one issue with the model suggested above is that it will be painful to answer a question like 'who are all the users in a given department?' because it requires a search through each array for each row. i'll try to quickly summarise some info here, but you'll probably have to search up a bit more as well to add on. in data modelling, this relationship between users and departments is known as a many-to-many (M-N) relationship https://en.wikipedia.org/wiki/Many-to-many_(data_model), where 1 user can be assigned to many departments, and 1 department can have many users. to enable the most flexibility and ease of use for yourself, it's generally a good idea to have a 1-Many relationship between tables (where a foreign key is set up in the Many table) e.g. a

files

table and

folders

table, and there's a

folder_id

foreign key in the

files

table. to do that in M-N cases, the solution tends to be creating an intermediate table (or composite table) e.g.

user_departments

that sits between the 2 tables. the simplest version just has 3 cols:

id, user_id, dept_id

, with

user_id

and

dept_id

being foreign keys to

users

and

departments

respectively. this breaks down the overall rs from M-N into two different 1-M relationships, giving you the advantages mentioned above

I see, and so then to get all of the departments as user belongs to, i would then query the

user_departments

table for all of the rows that have the

user_id

of the corresponding user?

yep and the same table the get all of the users in a given dept

Gotcha, alright i will give this a try . Thank you!