User roles and category restriction concept/theory help

Get Started
1

So I need to make a file sharing resource for a company, pdf/office docs, etc.

I’m needing restrictions based on permission levels and I’m a little confused on how to do this. For the user model, is it a join table?

Users (id, name)
Roles (id, name)
user_roles(id, user_id, role_id)

With a comma separated list column in the db with the role id’s available for that section? Then on each page load check that the user’s role list is contained in the page list?

OR

Is it easier to only allow a person into one category and then still assign the number list to each category? So for instance, there’s a ‘dealer’ and there’s a ‘distributor’, but in certain cases someone can be a dealer/distributor…*which with this method there would be three roles and with the other there would only be two.

Are either of these the right path? Thanks for any insight.

2

that’s pretty much what i wanted to say, yeah

:slight_smile:

3

Yeah, that’s probably true. Though unless you’re inserting thousands of records at one time, it’s probably not noticeable. At least it hasn’t been w/our db. Granted there’s no point using an extra ID column if you don’t really need one …

4

how much is 33% useless space? and degraded performance?

well, obviously, the answer is not much, if it’s a small table

:slight_smile:

5

that looks fine

:slight_smile:

6

the comma-delimited list is wrong, you’ll need a document_roles table

having an id for a relationship table is also wrong, just make the two FKs a composite PK

helps?

7

it does, thanks.

So it’d be
users (id, name)
roles (id, name)
user_roles(user_id, role_id)
category_roles(category_id, role_id)

Correct?

8

Yep, on a relationship table. We did use the AK in some cases. Though I admit, we weren’t terribly obsessed with space constraints for basic ints.

Not every db requires an auto number to be the table’s primary key. Though IIRC, mySQL does. But in either case you have a covering index, so how much of an issue is this with a small table table storing ints …?

9

Yeah, I probably should have prefaced my first comment with “Not that everyone needs to do this, but …” :wink:

10

degraded because you’re updating an extra index unnecessarily

11

Some of the tables were pretty large. But again it’s only 3 int columns. If it were 3 blob/clob columns I might feel differently…

and degraded performance?

Degraded how if you’ve got a covering index? I’m not being argumentative here, I’m genuinely curious as we’ve never had any big performance bogs using this structure. Though we’re using sql server not mysql.

12

in a ~relationship~ table? that would only be if the relationship table has child tables of its own! (which is exceedingly rare, and i would likely propagate the natural keys in that case anyway)

my point was that this –

CREATE TABLE user_roles 
( user_id INTEGER NOT NULL
, role_id INTEGER NOT NULL 
, [COLOR="Blue"]PRIMARY KEY ( user_id, role_id )[/COLOR]
, INDEX role_users ( role_id, user_id )
, FOREIGN KEY ( user_id ) REFERENCES users ( user_id )
, FOREIGN KEY ( role_id ) REFERENCES roles ( role_id )
);

is a lot better than this –

CREATE TABLE user_roles 
( [COLOR="Red"]id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY[/COLOR]
, user_id INTEGER NOT NULL
, role_id INTEGER NOT NULL 
, [COLOR="Blue"]UNIQUE ( user_id, role_id )[/COLOR]
, INDEX role_users ( role_id, user_id )
, FOREIGN KEY ( user_id ) REFERENCES users ( user_id )
, FOREIGN KEY ( role_id ) REFERENCES roles ( role_id )
);

not least because of where new rows are inserted due to clustering (which i believe defaults to the PK)

it’s just extraneous extra space and extra processing, and it is never used

13

the comma-delimited list is wrong

Agreed

having an id for a relationship table is also wrong, just make the two FKs a composite PK

It’s a matter of preference. I’ve worked with db’s that used an id column as an AK and it often came in handy in many cases. As long as any unique constraints are enforced having an id doesn’t harm anything.

14

Yep, updating to an extra Index is not necessary when it is not required. Hence it’s a degraded performance. :slight_smile: