Trying to figure out how to create a users table and grant permissions

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)
);

CREATE TABLE permissions
(
id SMALLINT UNSIGNED AUTO_INCREMENT),
directory VARCHAR(50),
description TEXT,

PRIMARY KEY (id)
);

CREATE TABLE users-permissions
(
user-id SMALLINT UNSIGNED),
permission-id SMALLINT UNSIGNED),
FOREIGN KEY (user-id) REFERENCES users (id),
FOREIGN KEY (permission-id) REFERENCES permissions (id)
);

Is this right?

Looks fine to me.
I’d add a composite primary key to the users-permissions table

PRIMARY KEY (user-id, permission-id)

1 Like

so its ok if that table has 2 field which are both foreign keys and primary keys?

Also what does that mean if I declare both fields as a composite primary key

That the combination of the two must be unique, so you can’t have the same user tied to the same directory twice.

thats useful
so if that ever is tried, the query will fail?

Yes.

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.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.