Re: DB design - schema

[Rick James]

Given...

CREATE TABLE User_company
user_id INT UNSIGNED NOT NULL, -- use same datatype as other table(s)
company_id INT UNSIGNED NOT NULL,
PRIMARY KEY(user_id, company_id),
INDEX(company_id, user_id) -- In case you need to SELECT users, given a company.
) ENGINE=InnoDB;

(I would drop the "tbl_" prefix as being noise.)

The semantics of this table would be "this user can see that company". It would be a many-to-many mapping.

To see what a user can see:
SELECT company_id FROM User_company WHERE user_id = ?

To allow user 3 to see company 7:
INSERT IGNORE INTO User_company (user_id, company_id) VALUES (3, 7);
(The IGNORE lets you avoid an error if the row already existed.)

To disallow 3 looking at 7:
DELETE FROM User_company WHERE user_id = 3 and company_id = 7;