sql - Mysql Many to Many relationship -

sql - Mysql Many to Many relationship -

i trying implement simple many many relationship between 2 tables. user , groups.

user --------- user_id user_name grouping ---------- group_id group_name usergroup ---------- user_id group_id

lets both user , grouping table each has 1000 entries.

i have create 1 admin user belongs groups. should create 1000 entries in usergroup table "admin" user?

can create boolean column "applicable_to_all_groups" in user table should checked first before selecting usergroup table?

any suggestion on doing right way appreciated.

well, there's no "true" solution kind of cases.

let's on pros / cons

solution 1, in usergroup table

pros

requests allowed groups easier write (no or clause)

cons

you have add together entry in table every time add together entry in grouping table. doable, of course, boring, , error-prone.

if want new user "which can related groups", you'll have rewrite procedures / triggers / whatever utilize have "up-to-date" usergroup table add together new thing.

solution2, flag (= boolean column)

pros

avoid unnecessary entries in db (well, minor point)

always "up-to-date", without additional work.

easy add together new user "all groups" rights (just set flag true)

cons

you'll have add together or clauses when requesting allowed groups (based on flag or on groupuser table)

a personal point of view

i go flag solution...

mysql sql database orm many-to-many