Im trying to figure out how to create a users table but giving each different user different permissions.
Heres what im thinking…
We need a users table to hold all the users
We also need a permissions table to hold all the folders to be given permission.
Lastly I’ll need a users-permissions table to link the two (so is it ok to have 2 foreign keys only in this table?
I think the structure of the tables will be like
CREATE TABLE users
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT),
first_name VARCHAR(50),
last_name VARCHAR(50),
…
PRIMARY KEY (id)
);
For example, if you have many user and many directory values a table might need to have the same user or same directory in more than one row. So they can’t be unique keys by themselves.
But the two of them together as a composite key means that different combinations can be allowed, but only once.